Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a requirement on calculating the YTM of (the sales Quantity of a Product/the sales quantity if the shop has the Product A)
Month | Product | Shop | Sales Quantity |
---|---|---|---|
1 | A | S1 | 2 |
1 | A | S2 | 3 |
1 | B | S1 | 5 |
2 | B | S2 | 2 |
2 | B | S1 | 1 |
2 | A | S2 | 1 |
3 | B | S1 | 3 |
3 | A | S2 | 1 |
3 | B | S1 | 7 |
For example, the Product = A,then the result:
Month 1:
(2+3)/(2+3+5)=0.5
Month 2:
1/1+2=0.5
Month 3:
1/1 =1
YTM = 0.5+0.5+1 =2
How to use expression to calculate above scenario?
Sum({<Product = {"A"},[Month] = {"<=$(=Num(Month(monthstart(max(Month)))))"} >}[Sales Quantity])
/
Sum({<Shop = p({1<Product = {'A'}>}Shop),[Month] = {"<=$(=Num(Month(monthstart(max(Month)))))"}>} [Sales Quantity])
The expression in above is incorrect.
The result will be:
2+3+1+1/2+3+5+2+1+1+3+1+7
Best Regards,
Louis
Where are you looking to use this expression? in a text box object or a chart?
May be you just need to Aggr() over Month for a text box object
Sum(Aggr(
Sum({<Product = {"A"},[Month] = {"<=$(=Num(Month(monthstart(max(Month)))))"} >}[Sales Quantity])
/
Sum({<Shop = p({1<Product = {'A'}>}Shop),[Month] = {"<=$(=Num(Month(monthstart(max(Month)))))"}>} [Sales Quantity]),
Month))
Hi Sunny,
In a chart.
Best Regards,
Louis
Did you try the above expression (Sum(Aggr()))? Is it not giving you the desired result?
Hi Sunny,
It does not work.
Is there any difference if i include the year in set analysis?
Sum(Aggr(
Sum({<Product = {"A"},[Year] = {$(=max([Year]))},[Month] = {"<=$(=Num(Month(monthstart(max(Month)))))"} >}[Sales Quantity])
/
Sum({<Shop = p({1<Product = {'A'}>}Shop),[Year] = {$(=max([Year]))},[Month] = {"<=$(=Num(Month(monthstart(max(Month)))))"}>} [Sales Quantity]),
Month))
Best Regards,
Louis
Try it I guess.... I am not 100% sure I understand what you have and what you want.... do you think you can share a qvf sample with us to take a look at what you have?
Hi Sunny,
I will try and make a sample.
Many Thx!
Best Regards,
Louis
Hi Sunny,
If i put in a text object, will it be difference in expression?
Best Regards,
Louis
In a chart you will get multiple values, whereas in a text box object you will get one... I was not sure what exactly are you trying to get?
I am still waiting for a sample from your end