Rank grouped by in KPI window

[Shalom1]

Hello,

I need to present the KPI window with the number of  HAULIER in Haulier Ranking.

I use the measure called [Haulier Ranking].

In the table it's easy: RANK([Haulier Ranking])

But then when I chose one of [Haulier Group] I'd like to see its real number in ranking instead of 1.

And more important is to create the information:

HAULIER GROUP  XYZ is 17th from 115(total).

I've tried this:

but most times it shows "1". Just for some of them: "53" or someting else. I dont'understand why.

Please help

[Oleg_Troyansky]

Shalom Shalom,

In order to get the original ranking, even when selections are made, you should add some Set Analysis, that would disregard these selections. For example, to always disregard all user selections, you can use {1}, like this:

{1} RANK([Haulier Ranking])

To disregard just some of the selections, list those fields that you want to disregard:

{<[Haulier Group]=, F1=, F2=>} RANK([Haulier Ranking])

You may not need to use the AGGR() function, but if you do - make sure to use both the internal and the external aggregations around AGGR(), and to apply the same Set Analysis in both.

To learn more advanced development techniques, including advanced Set Analysis and AGGR(), check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[Shalom1]

@Oleg_Troyansky 

Here's what I see after applying {1} RANK([Haulier Ranking])

And my second question, I'd like to have this when I choose {Haulier Group] from the filter pane:

That's why I 'm using this expression:

aggr(distinct rank({<[Haulier Group] = >}[Haulier_Ranking EndLoadDate]), [Haulier Group])

[Oleg_Troyansky]

Shalom,

I'm assuming that [Haulier Ranking] is a Master Measure - correct? In this case, we'd need to look into it, to understand why you are getting the results that you are getting. If it's a field, then you should use any kind of aggregation within the Rank().

Also - what version of Qlik Sense are you using? This syntax become available relatively recently...

In your second formula, you should disregard the selection of Haulier Group outside of the AGGR, and you should enclose AGGR() in any aggregation functions, like sum, min, or even only(). If you don't, then the default aggregation is only(), and by default it uses current selections as a scope, so your set analysis gets ignored. I'd use something like this:

{<[Haulier Group] = >} only(aggr(distinct rank([Haulier_Ranking EndLoadDate]), [Haulier Group]))

or, if it doesn't work with your version of Qlik Sense:

only( {<[Haulier Group] = >}

     aggr({<[Haulier Group] = >}

          rank([Haulier_Ranking EndLoadDate])

     , [Haulier Group])

)

Cheers,

[Shalom1]

Unfortunetaly your solution doesn't show any results 😞

result:

Haulier Ranking Measure is the sum of  the few KPIs calculated using a weighted average:

KPI_1 * 0,33 + KPI_2 * 0,33  + KPI_3 * 0,33 by Haulier Group

When I add " {1} " it shows results where volume is 0 for exemple for previous month:

Above I have chosen one Hulier Group from the list (with the volume of orders 3096)

[Oleg_Troyansky]

Well, now you have 2 preceding set analysis conditions listed one after another - I don't think that could fly. As a minimum, you should unite these into one Set Analysis.

If you want my help on this, you'll need to create a sample app and share it here. Otherwise, you'll need to continue troubleshooting on your end - it's hard for me to keep guessing what might be wrong in your pictures.

Cheers,

[Shalom1]

Ok great, I will try to prepare a sample app, how to share it in here?

[Oleg_Troyansky]