
Re: how can i show the %difference of 2 months if this month sales..?
Gysbert Wassenaar Oct 28, 2015 2:59 AM (in response to camiel van der werf)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?

comm189307.qvf 176.0 K

Re: how can i show the %difference of 2 months if this month sales..?
camiel van der werf Oct 28, 2015 3:22 AM (in response to Gysbert Wassenaar )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..?
camiel van der werf Oct 28, 2015 4:58 PM (in response to camiel van der werf)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..?
Gysbert Wassenaar Oct 29, 2015 2:34 AM (in response to camiel van der werf)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..?
camiel van der werf Oct 29, 2015 3:32 AM (in response to Gysbert Wassenaar )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?

test jeroen  kopie.qvf 3.9 MB

Re: how can i show the %difference of 2 months if this month sales..?
Gysbert Wassenaar Oct 30, 2015 2:25 AM (in response to camiel van der werf)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

comm189307_v2.qvf 3.9 MB

Re: how can i show the %difference of 2 months if this month sales..?
camiel van der werf Oct 30, 2015 9:08 PM (in response to Gysbert Wassenaar )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..?
Gysbert Wassenaar Nov 1, 2015 9:23 AM (in response to camiel van der werf)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..?
camiel van der werf Nov 7, 2015 12:06 PM (in response to Gysbert Wassenaar )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?

voorbeeld.qvf 3.8 MB








