Help with RangeSum Above aggregation function

[SaraiMoH]

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!!!!

[sunny_talwar · Accepted solution]

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

 

[SaraiMoH]

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 🙂

[sunny_talwar]

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 

[SaraiMoH]

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 

[sunny_talwar]

No problem at all