Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
SaraiMoH
Contributor II
Contributor II

Help with RangeSum Above aggregation function

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:2019-11-18 example KS.png

I am calculating the Accumulated diff like this:

diff.PNG

and the max like this:

max.PNG

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

Please find attached the qvw

Thanks!!!!

 

 

 

 

 

 

 

 

 

 

1 Solution

Accepted Solutions
sunny_talwar

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

 image.png

View solution in original post

5 Replies
sunny_talwar

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

 image.png

View solution in original post

SaraiMoH
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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