topic Re: Rank top 3 with 2 dimensions in App Development

Rank top 3 with 2 dimensions

Sun, 15 Nov 2015 00:00:39 GMT

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

Re: Rank top 3 with 2 dimensions

swuehl — Sun, 15 Nov 2015 13:36:06 GMT

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

Re: Rank top 3 with 2 dimensions

Sun, 15 Nov 2015 22:34:59 GMT

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

Re: Rank top 3 with 2 dimensions

swuehl — Mon, 16 Nov 2015 10:28:53 GMT

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?

Re: Rank top 3 with 2 dimensions

Mon, 16 Nov 2015 21:31:54 GMT

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?

Re: Rank top 3 with 2 dimensions

swuehl — Mon, 16 Nov 2015 22:04:39 GMT

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?

Re: Rank top 3 with 2 dimensions

swuehl — Mon, 16 Nov 2015 23:42:19 GMT

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

Re: Rank top 3 with 2 dimensions

Tue, 17 Nov 2015 06:46:25 GMT

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?

Re: Rank top 3 with 2 dimensions

swuehl — Tue, 17 Nov 2015 12:06:10 GMT

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

Re: Rank top 3 with 2 dimensions

Wed, 18 Nov 2015 07:00:08 GMT

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.

Re: Rank top 3 with 2 dimensions

swuehl — Wed, 18 Nov 2015 11:31:12 GMT

That's because in your set expression for the increase calculation, you filter for Brands with more than 5 thefts, not for the combination Brand&City. To enable this, create a field in the data model

LOAD

City,

Brand,

Autonumber(City & Brand) as CityBrandID,

And use this in your set analysis.

See attached

Re: Rank top 3 with 2 dimensions

Wed, 18 Nov 2015 16:07:15 GMT

Thank you very much! This is great!

Re: Rank top 3 with 2 dimensions

Sun, 22 Nov 2015 16:04:00 GMT

Hello Swuehl,

i got it almost in my dataset. i got the top 3. But i still see the others. See the attached file. The formula looks like the same as you. What am i doing wrong?

Re: Rank top 3 with 2 dimensions

swuehl — Sun, 22 Nov 2015 16:51:24 GMT

You just need to edit your chart, in chart properties, go to something called Add-On, data processing (? not sure, translated it from my language), then disable 'show NULL values'.

Re: Rank top 3 with 2 dimensions

Sun, 22 Nov 2015 17:08:50 GMT

I got it! thank you very much!