Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RadovanOresky
Partner Ambassador
Partner Ambassador

Last balance value in pivot

Hi,

I have a problem with getting the last value of balance sheet accounts in the pivot table. Does anybody has an expression which could do it?

My case is like this:

- closing balances are calculated in the script for every account and every day

- I need to get a pivot table where dimensions are month, week and accounts group

- I need to get an expression where for any shown period it sums the balances for all selected accounts but only from the last day of that period

I attached an excel whit desired result.

1 Solution

Accepted Solutions
RadovanOresky
Partner Ambassador
Partner Ambassador
Author

Not that it matters anymore, but I solved the problem with FirstSortedValue() function.

The expression was FirstSortedValue( Aggr( Sum(Balance), Date ), -Date)

This blog post by HIC expains the approaches to non-additive numbers, like Account Balance.

Additive and Non-Additive Numbers

Radovan

View solution in original post

3 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Do you have a calendar table in your QV data model?  If not, create one. There are lots of examples in this forum of how to do this.

One way of getting what you want is to ensure you have IsMonthEnd and IsWeekEnd fields in the calendar model. You can create these in the calendar load by:

If(Date=MonthEnd(Date),1,0)        AS     IsMonthEnd

,If(Date=WeekEnd(Date)-2,1,0)     AS     IsWeekEnd

Then, in your chart with Week as the last dimension your expression can be:

Sum({<IsWeekEnd={1}>} Balance)

And in the chart to month level:

Sum({<IsMonthEnd={1}>} Balance)

Hope this helps,

Jason

RadovanOresky
Partner Ambassador
Partner Ambassador
Author

Thanks, this works nicely in the restricted circumstances. Trouble is when adding accounts group dimension in front of the month. When I collapse, it will show the sum of balances, but the last balance from the last month is needed.

I used this expression, which works until date level, but without week level:

If(Sum({<IsMonthEnd={1}>}Balance)=0, Sum(Balance), Sum({<IsMonthEnd={1}>}Balance))

Then, when the pivot is expanded on monthly level it works fine, e.g.:

Account GroupMonthBalance
Group11100
Group12200

But when I collapse the group, the sum is incorrect, e.g.:

Account GroupBalance
Group1300 - but should be 200

Also, when I want to have month and week in the same pivot and I expand the expression, then when the date for the end of the week is the same as the date for the end of the month, value will duplicate.

So, I guess I hoped there is a more general function, which would always show the last value (timewise) for whichever dimension. I will try to optimize it further.

But really thanks.

RadovanOresky
Partner Ambassador
Partner Ambassador
Author

Not that it matters anymore, but I solved the problem with FirstSortedValue() function.

The expression was FirstSortedValue( Aggr( Sum(Balance), Date ), -Date)

This blog post by HIC expains the approaches to non-additive numbers, like Account Balance.

Additive and Non-Additive Numbers

Radovan