Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello guys,
i have the next question:
i have this information:
sales | ||||
cars | ||||
month | toyota | volvo | citroen | peugeot |
may | 5 | 7 | 6 | 4 |
june | 7 | 9 | 3 | 1 |
july | 9 | 12 | 0 | 3 |
aug | 15 | 11 | 1 | 7 |
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
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?
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?
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 ,
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
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.
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?
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
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:
city | brand car | values |
Groningen | toyota | 5% |
volvo | 14% | |
bmw | 18% | |
Amsterdam | toyota | 2% |
renault | 7% | |
mercedes | 9% | |
Rotterdam | volkswagen | 31% |
citroen | 9% | |
peugeot | 22% |
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?
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
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?