Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate rank on nested aggregation in Qliksense expression.

Hi,

I have 5 levels of data and granularity is from 5th level. I need to calculate rank of average value based on city.

so i have written the below calculation:

Rank(aggr(Avg( Aggr(Avg(Aggr(Avg((Aggr(avg(Score),distinct[Level 4]))),distinct[Level 3])),

distinct[Level 2])),[Level 1]),City)

But when I am trying to create a Table with dimension city,level1,leve2,level3,level4,level5 and Rank, its returning null value.

Please help as I am new to Qliksense.

My requirement is my cities should be ranked based on the total average of all levels value.

Thanks!!

7 Replies
vinieme12
Champion III
Champion III

Rank (aggr (avg (score),city,level1, level,2,level3, level4,level5),1,1)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

vinieme12

If there is no outer aggregation here, why do we even need Aggr() here? May be just this should suffice

Rank(Avg(score), 1, 1)

Having said that, I don't really understand the goal here. May be a sample might help here to help the OP better

Not applicable
Author

Hello Sunny,

Thanks for the reply,wil try it out. For better understanding of the requirement please check the image below  and let me know as how this could be done in Sense.Thanks!!Average sample data.png

Not applicable
Author

Hi Sunny,I tried out this logic but its not calculating the rank properly as I think it's taking the overall avg.

Ex. Bangalore is having rank26 actually but using this calculation its showing 16.

sunny_talwar

Can you provide your Excel sheet as an attachment

Uploading a Sample

vinieme12
Champion III
Champion III

try,

Rank(sum(score)/Count(City), 1, 1)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

No,the ranking of cities isn't proper using this logic .