Sum of latest values, with multiple dimensions

[BartVA]

I have a table with EndBalance values, by Company, Journal, BankStatementID ( = chronological order) and Date.

I need to show in a pivot the sum of the latest EndBalances by Company and Journal for a given day, week, quarter, year, ... (depending on the pivot level).

So I need to first get, for each Company and Journal, the EndBalance with the highest BankStatementID prior to the latest date in the selection (or pivot column or row).

Then I need to sum these LatestEndBalances to get the Total LatestEndBalance. And this is what I want to show in the pivot table.

Attached a sample file in Excel.

I tried several approaches but none do what I want, like

Sum({<BankStatementID = {"=BankStatementID = Max(BankStatementID)"}>} BalanceEnd)

(this works well until I take weeks for columns in the pivot, it sums all days of the week instead of taking the latest)

Sum( If( BankStatementID = Max(TOTAL BankStatementID), BalanceEnd) )

(this doesn't take into account the Company and Journal).

Any help is greatly appreciated.

(For information and transparency, this is a follow-up question to my earlier question "Weekly cash report (table with different formulas per row and per column)", working further on that I bumped into this new problem).

[Oleg_Troyansky · Accepted solution]

Hi there,

Your attempts to solve this problem in Set Analysis can't work, unfortunately, because Set Analysis cannot be sensitive to your chart dimensions, such as Company, Journal, Date, etc...

I'd suggest to use the function AGGR() for your nested aggregation (first, calculate the latest value and then sum them up), but your inner aggregation also depends on the level of the pivot table - which makes it extremely difficult to formulate it properly.

The only thing that comes to mind is to use the Dimensionality() function to determine the pivot table's level, and then use the appropriate level of aggregation with the function FirstSortedValue().

First, you need to find out the value of Dimensionality for your calendar fields - Date, Week, Quarter, Year. Let's say, for example, that Dimensionality = 6 for Date, 5 for Week, 4 for Quarter, and 3 for Year. Then your formula could look like this:

IF(Dimensionality() = 6,
   sum(AGGR(FirstSortedValue(BalanceEnd), -Date, Dim1, Dim2, ...), Dim1, Dim2, ...)),

IF(Dimensionality() = 5,
   sum(AGGR(FirstSortedValue(BalanceEnd), -Week, Dim1, Dim2, ...), Dim1, Dim2, ...)),

IF(Dimensionality() = 4,
   sum(AGGR(FirstSortedValue(BalanceEnd), -Quarter, Dim1, Dim2, ...), Dim1, Dim2, ...))
...

)))

The extra dimensions represent fields that you need to attribute the balance to - Company, Journal, etc. The second set of extra dimensions represent additional dimensions of the Pivot table. They also need to be included in the AGGR() calculation.

Cheers,

[BartVA]

I think I'll limit myself to using YearWeek as a time dimension. Would I have to use the following formula then?

sum(AGGR(FirstSortedValue(BalanceEnd), -YearWeek, Company, Journal), Company, Journal))

[Oleg_Troyansky]

Yes, that looks right.

[BartVA]

I still can't get it working I'm afraid. Since this specific question here (with the multiple dimensions) has been very well answered, I marked it as solved, and posted a new, specific question for the aggregation problem here: Sum of Latest Values in Week, in Pivot Table - Qlik Community - 2421345. With an export of the data table in question attached. Would be fabulous if you could have a look (or anyone else)...