topic Help with RangeSum Above aggregation function in QlikView

Help with RangeSum Above aggregation function

SaraiMoH — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I'm in desperate need of help since I've been trying several things but I can't make this work as it should so I would really appreciate it if someone could give me a hand.

I'm trying to calculate a statistic that gives the maximum difference between two columns that have the accumulated distribution (percentage) of a dimension. I could calculate the accumulated percentages and the difference using RangeSum Above but somehow when I try applying the max it doesn't work.

I attach the qvw and here is the pivot table I have:

I am calculating the Accumulated diff like this:

and the max like this:

But as you see in the image it doesn't work in all cases.

Please find attached the qvw

Thanks!!!!

Re: Help with RangeSum Above aggregation function

sunny_talwar — Mon, 18 Nov 2019 15:31:31 GMT

Are you using QlikView 12 or above... if you are, then try this for Max

=IF(DIMENSIONALITY() = 1 ,MAX(AGGR( RangeSum(Above( SUM( {<PERIOD_LABEL={'Monitoring Period'}>} SUM_IX) / SUM( {<PERIOD_LABEL={'Monitoring Period'}>} TOTAL <COUNTRY_CODE> SUM_IX) - SUM( {<PERIOD_LABEL={'Development Period'}>} SUM_IX) / SUM( {<PERIOD_LABEL={'Development Period'}>} TOTAL <COUNTRY_CODE> SUM_IX) , 0, RowNo() ) ) ,COUNTRY_CODE, (SUBGRADE, (TEXT)) ) ) ) // FirstSortedValue

Re: Help with RangeSum Above aggregation function

SaraiMoH — Mon, 18 Nov 2019 16:06:17 GMT

Thank you very much Sunny! It works now 😄

Yes, I'm using qlikview 12.20 I'm just wondering what does the (SUBGRADE, (TEXT) is doing?

Have a nice day 🙂

Re: Help with RangeSum Above aggregation function

sunny_talwar — Mon, 18 Nov 2019 16:14:50 GMT

Aggr() sort your dimensions in the load order which could be different from the order you are using in the chart. Why it makes a difference? It is because you are doing accumulation which is dependent on the order of dimension. In order to get the correct order... which it seems was the text sorting of your second dimension... we use this syntax. Syntax itself can be understood by reading the below article.

sortable Aggr function

Re: Help with RangeSum Above aggregation function

SaraiMoH — Mon, 18 Nov 2019 16:29:13 GMT

Great! I'm so thankful since I was quite frustrated after trying many different things and without your help, I don't think I would have been able to make it work. Thanks for your support

Re: Help with RangeSum Above aggregation function

sunny_talwar — Mon, 18 Nov 2019 16:34:03 GMT

No problem at all