Calculation with Rank () or Aggr()

[male_carrasco]

Hi all!

I need to create a city-based ranking in a Qlik Sense Combo Chart. The dimension is 'City,' the bars represent total sales, and a point displays the % TOP3(sales)/Total for each city. In the dashboard, an Agency is always selected, so the calculation should remain unaffected by the agency selection.

I've successfully calculated the total using the following formula: aggr(sum( {<Agencies=>} Sales), City).

However, I can't find the correct formula to calculate the TOP 3, I tried:

sum( {<Agencies={"=Rank(sum({<Agencies=>} Sales))<=3"}>} Sales) but the result is not correct.

I need to display it in a combo chart like:

I would appreciate your assistance in resolving this matter

Data Example:

@marcus_sommer 
@fkeuroglian 
@chematos 

[Oleg_Troyansky · Accepted solution]

Hi there!

I think the core problem is this... You need to rank Agencies in the context of each City, not globally. Your Set Analysis condition is absolutely correct, however it calculates the ranking of Agencies globally, with no regard to your chart dimension. Set Analysis is blind to your chart dimensions, remember?

In order to calculate what you need, I'd recommend using a similar condition, but in an IF() function within an AGGR(). Something like this:

 {<Agencies=>}  sum(AGGR(IF(RANK(...) <=3, sum(Sales)), Agencies, City)) 

This way, you will get your agencies ranked within each city, and the result will contain sales for the top 3 agencies in each City.

I teach complex analyses like this one at my session on advanced Set Analysis and AGGR() at the Masters Summit for Qlik. See if you can join us in Orlando or in Dublin this fall. You will learn many advanced techniques like this one!    

[male_carrasco]

Hi Oleg!

Thanks for your help! 🙌

I applied your solution, but it still showed me the incorrect result:

I used this expression:

{<Agencies=>} sum(AGGR(IF(RANK(sum({<Agencies=>} Sales)) <=3, sum(Sales)), Agencies, City))

Additionally, In my data model, the fields are on different tables:

PS: I'd like to visit Orlando or Dublin, but if I can't, I'll keep an eye out if you have any online spots available.

Regards!!!!!!!!!!!

[Oleg_Troyansky]

Hi there,

I'd need a bit more details about what's wrong,  to offer any more ideas... If you could create a sample app and share it here, I would be able to take a look.

I'll be traveling for a few days, so I may not be able to look into it any further for a few days. Remind me next week if you are still having a problem.

Cheers,

[male_carrasco]

Hi Oleg,

I made some changes in the app, and your solution was successful.
Thank you so much!!!