# Qlik Sense App Development

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) Groningen toyota 5% volvo 14% bmw 18% Amsterdam toyota 2% renault 7% mercedes 9% Rotterdam volkswagen 31% citroen 9% peugeot 22%

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

## 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

## 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

## 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

## 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

## 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

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