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

how can i show the %difference of 2 months if this month sales..?

hello guys,

i have the next question:

i have this information:

   

sales
cars
monthtoyotavolvocitroenpeugeot
may5764
june7931
july91203
aug151117

and i want a barchart by car with the % difference between the second last month (july) and the third last month (june). But i only want to see the cars where the sales of the second last month are higher then 5 and the sales of the third last month are higher then 5. So i don't want to see citroen and peugeot.

I tried it with this formula in the measurement and cars dimension:                                                                                                                                                                  

 

If(sum({$<MONTH={"$(=max({1} MONTH)-1)"}>}SALES)>5 and

sum({$<MONTH={"$(=max({1} MONTH)-1)"}>}SALES)>5 ,

(Sum({$<MONTH={"$(=max({1} MONTH)-1)"}>}SALES)-

Sum({$<MONTH={"$(=max({1} MONTH)-2)"}>}SALES))/

Sum({$<MONTH={"$(=max({1} MONTH)-2)"}>}SALES)

  But it's not working, what i'm a doing wrong? i hope somebody can help.

greetings,

camiel

1 Solution

Accepted Solutions
Gysbert_Wassenaar

You're probably having problems with the month field. You can try creating an additional numeric month field and use that in the set analysis expressions. See attached example. Is that what you're looking for?


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
Gysbert_Wassenaar

You're probably having problems with the month field. You can try creating an additional numeric month field and use that in the set analysis expressions. See attached example. Is that what you're looking for?


talk is cheap, supply exceeds demand
Not applicable
Author

Hello Gysbert,

Thanks for you're respons!

No, i think that's not the problem. Because i have the months already numeric. I'm sorry i've not told that in my example, i thought if i show jan, feb, mar .... in the example it's better to read for you guys.

But this part of the formula works:

(Sum({$<MONTH={"$(=max({1} MONTH)-1)"}>}SALES)-

Sum({$<MONTH={"$(=max({1} MONTH)-2)"}>}SALES))/

Sum({$<MONTH={"$(=max({1} MONTH)-2)"}>}SALES)


Only if i add the next part (if....) of the formula it's going wrong:

If(sum({$<MONTH={"$(=max({1} MONTH)-1)"}>}SALES)>5 and

sum({$<MONTH={"$(=max({1} MONTH)-1)"}>}SALES)>5 ,

Not applicable
Author

Hello Gysbert,

I looked a second time to your solution. First i thought it's not doing what i want. but it does exactly what i want. But it's a different formula then i tried. Thanks for it!

is there a way that my formula works also with if?

greetings,

camiel

Gysbert_Wassenaar

is there a way that my formula works also with if?

I can't answer that question since I don't know the source data you used and how you created the MONTH field.


talk is cheap, supply exceeds demand
Not applicable
Author

Hello Gysbert,

i have attached the file, so you can see the data. You're formula is also not working in my sheet:

Sum({<AANTAL_GESTOLEN={'>0'},JAAR_MAAND={'$(=max(JAAR_MAAND)-1)'}>}AANTAL_GESTOLEN)/

Sum({<AANTAL_GESTOLEN={'>0'},JAAR_MAAND={'$(=max(JAAR_MAAND)-2)'}>}AANTAL_GESTOLEN) -1

You see the barchart now. But i have now: >0 if i change it to >5, i don't see anaything anymore. If you look to the figures it's bigger then 5, so i had to something. Do you see what i'm doing wrong?

Gysbert_Wassenaar

Actually this is my mistake, not yours. If( sum(Q) > 5,...) is something different than sum(if(Q>5) ). You want only the brands with more than 5 thefts in the two periods you're comparing. Ok, try this one:

Sum({<JAAR_MAAND={'$(=max(JAAR_MAAND)-1)'},MERK_BESCHR={"=sum({<JAAR_MAAND={'$(=max(JAAR_MAAND)-1)'}>}AANTAL_GESTOLEN)>5"}>}AANTAL_GESTOLEN)/

Sum({<JAAR_MAAND={'$(=max(JAAR_MAAND)-2)'},MERK_BESCHR={"=sum({<JAAR_MAAND={'$(=max(JAAR_MAAND)-2)'}>}AANTAL_GESTOLEN)>5"}>}AANTAL_GESTOLEN) -1


talk is cheap, supply exceeds demand
Not applicable
Author

Hello Gysbert,

Great! it's working! thanks a lot. But now i wanted to make a dimension limitation on 2 dimensions. i can do it on 1 dimension. But i do not get it on 2, then he takes them separated. By example;    I want the top 3 theft of cars by city and by brand of cars. So for example these are the outcomes:

   

citybrand carvalues
Groningentoyota5%
volvo14%
bmw18%
Amsterdamtoyota2%
renault7%
mercedes9%
Rotterdamvolkswagen31%
citroen9%
peugeot22%

then he have to show only rotterdam Volkswagen, Rotterdam peugeot and Groningen bmw. if i make a dimension of city and brand i can do a limitation on both, but not in a combination. is that possible?

Gysbert_Wassenaar

You'll have to use the rank function for that. This scenario is described on page 3 and 4 of this document: Show Top Performers


talk is cheap, supply exceeds demand
Not applicable
Author

Hello Gysbert,

Sorry for my late response. I had vacation for some days. Thanks for the document. I have read it. But i don't get it right like the example i showed above. i don't get a rank on a combination of 2 dimensions.

See the appendix for my  app so far.

the right bar graph is close to what i want. But does not give a rank by brand(merk in dutch) and city(woonplaats in dutch). He gives a rank by city and then the first 5 city's. In the left bar graph i tried it with rank, but it's not going good.

i want the top 5 of difference between this month and last month in a city of a brand.

What am i doing wrong?