9 Replies Latest reply: Nov 7, 2015 12:06 PM by camiel van der werf

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

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

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?

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

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 ,

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

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

• ###### Re: how can i show the %difference of 2 months if this month sales..?
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.

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

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?

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

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

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

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?

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

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

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

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?