topic Re: Top 10 Debtors for Rolling 12 Month in App Development

Top 10 Debtors for Rolling 12 Month

HurikanLorenzo — Sat, 16 Nov 2024 19:29:26 GMT

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

Re: Top 10 Debtors for Rolling 12 Month

hansvillo — Wed, 11 Dec 2019 08:32:14 GMT

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

Re: Top 10 Debtors for Rolling 12 Month

HurikanLorenzo — Thu, 12 Dec 2019 00:51:01 GMT

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].

Re: Top 10 Debtors for Rolling 12 Month

HurikanLorenzo — Mon, 27 Jan 2020 21:56:29 GMT

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.