7 Replies Latest reply: Jan 9, 2017 7:55 AM by ANKITA BEHERA

# 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.

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

Thanks!!

• ###### Re: How to calculate rank on nested aggregation in Qliksense expression.

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

• ###### Re: How to calculate rank on nested aggregation in Qliksense expression.

Vineeth Pujari

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

• ###### Re: How to calculate rank on nested aggregation in Qliksense expression.

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

• ###### Re: How to calculate rank on nested aggregation in Qliksense expression.

Can you provide your Excel sheet as an attachment

• ###### Re: How to calculate rank on nested aggregation in Qliksense expression.

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.

• ###### Re: How to calculate rank on nested aggregation in Qliksense expression.

try,

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

• ###### Re: How to calculate rank on nested aggregation in Qliksense expression.

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