Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
Rank (aggr (avg (score),city,level1, level,2,level3, level4,level5),1,1)
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
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!!
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.
Can you provide your Excel sheet as an attachment
try,
Rank(sum(score)/Count(City), 1, 1)
No,the ranking of cities isn't proper using this logic .