Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
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 II
Champion II

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

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 II
Champion II

try,

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

Not applicable
Author

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