topic Re: Outstanding balance pivot table Set analysis in QlikView

Outstanding balance pivot table Set analysis

Thu, 16 Jul 2015 14:42:28 GMT

Hello,

I have daily account balances snapshots and using set analysis to calculate outstanding totals.

The formula sum ({<BalanceDate = {'$(=Max(BalanceDate))'} >} Balance) does work for the latest total but doesn't consider month/day dimensions

Please advise

Re: Outstanding balance pivot table Set analysis

MK_QSL — Thu, 16 Jul 2015 14:46:05 GMT

In Set Analysis, Set Expression is evaluated before the aggregation made. So in this case, Max(BalanceDate) is calculated first which will remain same for all lines. You can't have Max(BalanceDate) for each line different using set analysis.

Re: Outstanding balance pivot table Set analysis

Thu, 16 Jul 2015 16:16:16 GMT

Following the advice I've modified the formula

FirstSortedValue ( Aggr( Sum(Balance), BalanceDate) , -BalanceDate)

and it works in test example

but it loosing calculation if extra dimension added, Company for example

Re: Outstanding balance pivot table Set analysis

MK_QSL — Thu, 16 Jul 2015 17:21:33 GMT

Add company in Aggr