Top 10 Debtors for Rolling 12 Month

[HurikanLorenzo]

Hi 

I am trying to output Top 10 Debtors balance per month for the last 12 months in a chart, dimension Month and Measure Balance (see attachment). I am able to code it using set analysis, however it shows same Top 10 Debtors as ranked for last month for the rolling period of 12 months. I would like to show Top 10 Debtors to be calculated for each months of rolling 12 months. Measure is defined like below and uses AsOfCalendar approach:

Sum(
{$<
DateNum={">=$(=num(AddMonths(date($(vMaxSnapshotDateNumPrev)),-11)))<=$(=$(vMaxSnapshotDateNumPrev))"},
[%Flag_IsThisDate]={1},
[Debtor]={"=Rank(AGGR(Sum({<DateNum={'$(=$(vMaxSnapshotDateNumPrev))'},[%Flag_IsThisDate]={1}>}[Open Amount]*($(vCurrencyRate))),[Debtor]),4)<=10"}
>}
[Open Amount]*$(vCurrencyRate))

Any ideas?

Thanks for help in advance, M.

TOP 10 Rolling 12 Months

[hansvillo]

Hi Lorenzo,

For making a top 10 you can use the Qlik sense functionality ( see picture below).  You can create a selection where you select the months.

I hope you understand

[HurikanLorenzo]

Hi Hansvillo

Chart I am using has only one dimension [Month Year] and TOP 10 standard functionality does not offer ranking against dimension that is not in the chart, i.e. [Debtor].

[HurikanLorenzo · Accepted solution]

Hi there

In case somebody else is stuck with the same requirement it turned out that my initial approach was not correct. Correct solution is to use summation over multiple aggregation in calculated dimension (see attached). Later approach logically solved requirement, however it ran into timed out calculation issue that was resolved by using key fields in set analysis restriction.