Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

DateYear-PeriodYEARMONTHACCOUNT# BALANCE
1/31/102010-0120101A100
2/28/102010-0220102A200
3/31/102010-0320103A300
4/30/102010-0420104A400
5/31/102010-0520105A500
6/30/102010-0620106A600
7/31/102010-0720107A700
8/31/102010-0820108A800
9/30/102010-0920109A900
10/31/102010-10201010A1000
11/30/102010-11201011A1100
12/31/102010-12201012A99
12/31/102010-12201012A1200
1/31/112011-0120111A110
2/28/112011-0220112A120
3/31/112011-0320113A130
4/30/112011-0420114A140
1/31/102010-0120101B101
2/28/102010-0220102B202
3/31/102010-0320103B303
4/30/102010-0420104B404
5/31/102010-0520105B505
6/30/102010-0620106B606
7/31/102010-0720107B707
8/31/102010-0820108B808
9/30/102010-0920109B909
10/31/102010-10201010B1010
11/30/102010-11201011B1111
12/31/102010-12201012B1212

[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!

1 Solution

Accepted Solutions
sunny_talwar

Try FirstSortedValue function here:

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

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

Try FirstSortedValue function here:

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

Capture.PNG

Not applicable
Author

Thanks Sunny, that's it!