I have general ledger balances which are semi-additive (i.e. you can add across accounts to sum assets in any given period, but it is incorrect to add the balances across the time dimension). I need to build a set expression that will correctly choose the maximum period within a range of periods as part of a set analysis expression.
For instance, when the user has collapses the dimension from individual months, to quarters, or years, the expression should calculate the balance as of the last period in the quarter or year. Essentially, within each row of a straight table or chart, the expression should note the reporting dimension (year, quarter, month), get the maximum possible period for that dimension, and calculate the balance as of that period. So for 2015-Q1, the expression needs to determine that '2015-03' is the [Year-Period] value, and return the balance for that period as the result. Same for Q2, Q3, etc.
Here is a sample of balances by reporting period:
Year-Period | Year-Qtr | # Balance |
|---|
2015-01 | 2015-Q1 | $6,544,942 |
2015-02 | 2015-Q1 | $6,743,690 |
2015-03 | 2015-Q1 | $5,478,268 |
2015-04 | 2015-Q2 | $5,742,846 |
2015-05 | 2015-Q2 | $5,850,625 |
2015-06 | 2015-Q2 | $5,695,024 |
2015-07 | 2015-Q3 | $5,893,398 |
2015-08 | 2015-Q3 | $5,917,999 |
2015-09 | 2015-Q3 | $6,509,933 |
2015-10 | 2015-Q4 | $6,588,746 |
2015-11 | 2015-Q4 | $7,111,069 |
2015-12 | 2015-Q4 | $5,873,844 |
For '2015-Q1', the balance in the expression should be $5,478,268, the balance for the maximum period in that range. Same for the year--if the user clicks to report by year, then 2015 balance should be $5,873,844.
In a straight table I can get Qlikview to indicate the maximum value of the [Year-Period] field, as shown in the table below. However, that doesn't work when referenced in a set expression. (Note that the set values shown have been expanded; actual formula in the expression are below).
Year-Qtr | =SUM(DISTINCT [# Balance]) | =Max([Year-Period]) | =SUM(DISTINCT {$< [Year-Period] = {'=2015-12'} >} [# Balance] ) | =SUM(DISTINCT {$< [Year-Period] = {'2015-12'} >} [# Balance] ) |
|---|
| $73,950,383 | 2015-12 | $73,950,383 | $5,873,844 |
2015-Q1 | $18,766,900 | 2015-03 | $18,766,900 | $0 |
2015-Q2 | $17,288,495 | 2015-06 | $17,288,495 | $0 |
2015-Q3 | $18,321,330 | 2015-09 | $18,321,330 | $0 |
2015-Q4 | $19,573,658 | 2015-12 | $19,573,658 | $5,873,844 |
The first set expression (fourth column) is:
=SUM(DISTINCT
{$< [Year-Period] = {'=$(=Max([Year-Period]))'} >}
[# Balance]
)
This expression is calculating the sum of all three periods in each quarter (same as the simple sum calculation in column 2).
The second set expression (fifth column) is:
=SUM(DISTINCT
{$< [Year-Period] = {'$(=Max([Year-Period]))'} >}
[# Balance]
)
This expression returns the correct value for Q4, but for no other.
The question is: How can I dynamically get the maximum value for the [Year-Period] field, for each row, when the dimension is collapsed from periods to quarters and years? This value needs to be referenced in the set expression to drive the calculation of the correct balance at the end of each period.
QVW is attached. Thanks!
