Simple Ledger That Adds Category Totals

1

Hi,

I’ve set up a simple ledger and I would like to add the totals for each category. I have two separate tables as below (not linked views of the same table.)

My [Total] column formula in the [Simple Ledger] works fine: =thisTable.filter(Date<=thisRow.Date).Amount.Sum()

However, the [Category Total] in the [Category] table is only getting the sum for the blank category. The formula I am using is: =[Simple Ledger].Filter(Category=thisRow.Name).Amount.Sum()

Any help would be appreciated.

Thanks.

image
image.png989×633 12.4 KB

1 Like
2

I figured out that the problem is that the [Category] in the [Simple Ledger] is not actually text but the reference to the entire row, so it is not matching the text. I just need a suggestion on how to modify the formula so I can evaluate apples to apples.

1 Like
3

Hi All,

Sorry, I’ve been wracking my brain on it for a few hours tonight and I’ve finally figured it out myself.

In case anyone is interested, I found two different solutions with slightly different results.

  1. This is referencing the actual row reference that is being set in [Simple Ledger].[Category]: =[Simple Ledger].Filter(Category=thisRow).Amount.Sum()

The problem (or not a problem, how you want to look at it) with this method is that blank rows are not being totaled.

  1. [Simple Ledger].Filter(Category.ToText()=Name).Amount.Sum()

This will convert row lookup to text first, and then match it to [Name]. This could be problematic in other ways. Personally, I find that referencing the actual row gives a tighter result.

Thanks,

Thomas

4 Likes
4

This is a common issue I see lots of people running into so glad you shared your solutions and figured this one out.
Would love you know how you figured out it was a data mismatch issue.

5

I read in one of the tutorials or instructions that the lookups point to the entire row, not just the column you have set as the display column. That helped clue me in since I was trying to compare text, but it was actually finding an entire row.

Now that I’ve wrapped my head around that, it all seems to be working really great!

1 Like