How to calculate value for maximum date/period for balance sheet values

Fri, 11 Mar 2016 00:39:25 GMT

I've got financial data that includes ending period balances that are semi-additive. That is, it is correct to sum the closing balances across a number of individual accounts that comprise, say, "Current Assets"; however, it would not be correct to sum the balances across the time dimension because the values represent snapshots at the end of each period.

I need a formula to chart the account balances over various levels of time granularity--e.g. by month or period, by quarter, and by year. The chart works perfectly at the most granular level of individual accounting periods (for the field [Year-Period]). However, whenever the dimension is collapsed to a quarter, or a fiscal year, the aggregation I've built fails because it sums the balance amounts for different periods, which is incorrect. I've hit on the idea of grabbing the maximum value of the [Year-Period] field as part of a set analysis shown in the formulae below, but this isn't working.

I created an example QVD (attached) with data similar in structure to the data I'm working with. The table below is the data set.

Example Data

Date	Year-Period	YEAR	MONTH	ACCOUNT	# BALANCE
1/31/10	2010-01	2010	1	A	100
2/28/10	2010-02	2010	2	A	200
3/31/10	2010-03	2010	3	A	300
4/30/10	2010-04	2010	4	A	400
5/31/10	2010-05	2010	5	A	500
6/30/10	2010-06	2010	6	A	600
7/31/10	2010-07	2010	7	A	700
8/31/10	2010-08	2010	8	A	800
9/30/10	2010-09	2010	9	A	900
10/31/10	2010-10	2010	10	A	1000
11/30/10	2010-11	2010	11	A	1100
12/31/10	2010-12	2010	12	A	99
12/31/10	2010-12	2010	12	A	1200
1/31/11	2011-01	2011	1	A	110
2/28/11	2011-02	2011	2	A	120
3/31/11	2011-03	2011	3	A	130
4/30/11	2011-04	2011	4	A	140
1/31/10	2010-01	2010	1	B	101
2/28/10	2010-02	2010	2	B	202
3/31/10	2010-03	2010	3	B	303
4/30/10	2010-04	2010	4	B	404
5/31/10	2010-05	2010	5	B	505
6/30/10	2010-06	2010	6	B	606
7/31/10	2010-07	2010	7	B	707
8/31/10	2010-08	2010	8	B	808
9/30/10	2010-09	2010	9	B	909
10/31/10	2010-10	2010	10	B	1010
11/30/10	2010-11	2010	11	B	1111
12/31/10	2010-12	2010	12	B	1212

[Year-Period] as the Dimension

When the aggregations are performed to sum by account, by period, the following expression works correctly:

=SUM(
{$< ACCOUNT = {'A'} >}
[# BALANCE]
)

For a chart where [Year-Period] is set as the dimension, the formula returns the value 1299, as expected, for period 2010-12. Again, [Year-Period] is the most granular dimension level.

[Year] as the Dimension

However, when the dimension is collapsed to years, the formula returns the value 7899, which is the sum of all of the values during the year. I attempted to use AGGR to calculate, but this doesn't work (it works for [Year-Period] as the dimension, same as the formula above):

=AGGR(

SUM(

{$< ACCOUNT = {'A'} >}

[# BALANCE]),

[Year-Period]

)

Within the chart, for dimension using [YEAR], the formula `MAX([Year-Period])` correctly returns "2010-12" for 2010 and "2011-04" for 2011. However, when this formula is used with a set analysis, it calculates the maximum value of [Year-Period] for the entire field, not for the dimension row.

=sum(
AGGR(
   SUM(
     {$< ACCOUNT = {'A'},
        [Year-Period] = {'$(=Max([Year-Period]))'}
      >}
     [# BALANCE]),
    [Year-Period]
   )
)

The formula above calculates a balance of 140 for 2011 (which is correct). However, it returns a 0 for 2010, when it should return 1299.

The Question

What is the best way to return a calculation that always references the maximum value of the [Year-Period], regardless of whether the dimension is set to years, quarters, or periods? I need the formula to look within the set of values that correspond to each dimension row, find the maximum value (i.e. 2010-12 would be the maximum value for the year 2010, while 2010-06 would be the maximum value for Q2-2010), and sum the balances that match that period for a given set of accounts.

Appreciate any help!

Re: How to calculate value for maximum date/period for balance sheet values

sunny_talwar — Fri, 11 Mar 2016 07:50:19 GMT

Try FirstSortedValue function here:

FirstSortedValue({<ACCOUNT = {'A'}>}Aggr(Sum({<ACCOUNT = {'A'}>}[# BALANCE]), [Year-Period]), -Aggr([Year-Period], [Year-Period]))

Re: How to calculate value for maximum date/period for balance sheet values

Fri, 11 Mar 2016 13:39:42 GMT

Thanks Sunny, that's it!

topic Re: How to calculate value for maximum date/period for balance sheet values in QlikView

How to calculate value for maximum date/period for balance sheet values

Example Data

[Year-Period] as the Dimension

[Year] as the Dimension

The Question

Re: How to calculate value for maximum date/period for balance sheet values

Re: How to calculate value for maximum date/period for balance sheet values