Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

14 Replies
swuehl
MVP
MVP

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
Author

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

swuehl
MVP
MVP

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
Author

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?

swuehl
MVP
MVP

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?

swuehl
MVP
MVP

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
Author

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?

swuehl
MVP
MVP

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

Not applicable
Author

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.