Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Rank top 3 with 2 dimensions

Hello,

I want the top 3 theft of cars by 2 dimensions city and by brand of cars. So for example this is my table:

  

city (dimension)brand car (dimension)car theft (expression)
Groningentoyota5%
volvo14%
bmw18%
Amsterdamtoyota2%
renault7%
mercedes9%
Rotterdamvolkswagen31%
citroen9%
peugeot22%

Then i want to show only rotterdam Volkswagen (31%), Rotterdam Peugeot(22%) and Groningen bmw(18%). I got already a document with the title ' show top  performers'. This document is telling about how you can rank with the formula rank and agr. I tried a lot of things with this. But i do not get it right. Does somebody know how you can do something like this?

regards,

Camiel

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Rank top 3 with 2 dimensions

Create a straight table chart with dimension city and brand car and as expression

=Only(

     Aggr(

          If( Rank(TOTAL YourChartExpressionComesHere)<=3, YourChartExpressionComesHere,0)

     , city, [brand car])

     )

YourChartExpressionComesHere being the car theft expression (Would be also good to know how this looks like).

14 Replies
MVP
MVP

Re: Rank top 3 with 2 dimensions

Create a straight table chart with dimension city and brand car and as expression

=Only(

     Aggr(

          If( Rank(TOTAL YourChartExpressionComesHere)<=3, YourChartExpressionComesHere,0)

     , city, [brand car])

     )

YourChartExpressionComesHere being the car theft expression (Would be also good to know how this looks like).

Not applicable

Re: Rank top 3 with 2 dimensions

Hello swuehl,

Thank you very much! This is great! the chart expression was: sum(theft) . in the example i maked it % but that was just for example.

When i was using your formula i saw also all the zero values. Now i get them away by limitation on the dimensions city and brand car, limitation is: relative value   > 0. But i think there is a better way for that. isn't it?

Again thanks for helping and so fast, really nice!

regards,

camiel

MVP
MVP

Re: Rank top 3 with 2 dimensions

If the option 'suppress zero values' on presentation tab of the straight table is enabled, I think you don't need a dimension limit.

Are you calculcating the percentages by enabling the 'relative' option? Or by using a theft expression like

=sum(theft) / sum(TOTAL<city> theft)

? Or are you looking at absolute values now?

Not applicable

Re: Rank top 3 with 2 dimensions

True.

This absolute values was just an example. I'm actually looking for the top 3 increase of theft this month against last month by brand and city where the theft had to be at a minimum of 5. So i had this formula for that piece:

Sum({<year_Month={$(vYearMonth)},Brand={"=sum({<year_Month={$(vYearMonth)}>}theft)>5"}>}theft)/

Sum({<YearMonth={$(vPreviousYearMonth)},Brand={"=sum({<YearMonth={$(vPreviousYearMonth)}>}theft)>5"}>}theft)-1

Now i have to put somewhere 'TOTAL' in this formula and i discovered that i now have a minimun of 5 thefts bij brand but not by brand and city. So i thougt it was easy now. But it's harder then a thought.

Do you know how i can do this?

MVP
MVP

Re: Rank top 3 with 2 dimensions

If the percentage is giving the increase in theft from one month to the next, I don't think you need the Total qualifier here. Or maybe I don't really get what you are doing. Could you upload a small sample QVW or some sample (mock up) data?

MVP
MVP

Re: Rank top 3 with 2 dimensions

I've set up a sample with your theft expression, seems to work for me using the above expression for the TOP3 table.

Not applicable

Re: Rank top 3 with 2 dimensions

Hello Swuehl,

Thank you!

But i do not have qlik view (QVW). I'm sorry, but can you also post it in qlik sense or if you don't have qlik sense can you post your formula?

MVP
MVP

Re: Rank top 3 with 2 dimensions

Basically, it's just a demo of the expressions we used above with some mock up data.

Not applicable

Re: Rank top 3 with 2 dimensions

Hello swuehl,

Thank you! That's almost exactly what i want. But in your example is the increase by Utrecht Citroen 200%. The theft in Oktober is 1 and in November is 3, so 200%. But i don't want to see the ones where the car theft in a month by brand and city is lower then 5. Because this 200% sounds like al lot decrease, but a theft of 3 is still almost nothing and not interesting for us.  So for example the theft  is for Citroen in Utrecht in Oktober 6 and in November 18, so the increase is also 200%, then it's interesting. But it's not when it's lower then 5 in a month by city and brand.