Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to get dynamic end of period date in a set expression

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!

1 Solution

Accepted Solutions
Highlighted
Not applicable

I figured it out. Following expression works:

FirstSortedValue(
  
AGGR( SUM([# Balance]), [Year-Period]), -AGGR([Year-Period],[Year-Period])
)
 

View solution in original post

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Use FirstSortedValue([# Balance], - [Year-Period]) instead.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

That doesn't work - returns null. Did you try it in the QVD?

Highlighted
Not applicable

I figured it out. Following expression works:

FirstSortedValue(
  
AGGR( SUM([# Balance]), [Year-Period]), -AGGR([Year-Period],[Year-Period])
)
 

View solution in original post

Highlighted
Not applicable

Thanks a lot for this one! Saved my day!