10 Replies Latest reply: Jan 31, 2017 11:16 PM by Louis Lam

# How to set analysis in YTM with calculation

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)

MonthProductShopSales Quantity
1AS12
1AS23
1BS15
2BS22
2BS11
2AS21
3BS13
3AS21
3BS17

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

• ###### Re: How to set analysis in YTM with calculation

Where are you looking to use this expression? in a text box object or a chart?

• ###### Re: How to set analysis in YTM with calculation

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

• ###### Re: How to set analysis in YTM with calculation

Hi Sunny,

In a chart.

Best Regards,

Louis

• ###### Re: How to set analysis in YTM with calculation

Did you try the above expression (Sum(Aggr()))? Is it not giving you the desired result?

• ###### Re: How to set analysis in YTM with calculation

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

• ###### Re: How to set analysis in YTM with calculation

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?

• ###### Re: How to set analysis in YTM with calculation

Hi Sunny,

I will try and make a sample.

Many Thx!

Best Regards,

Louis

• ###### Re: How to set analysis in YTM with calculation

Hi Sunny,

If i put in a text object, will it be difference in expression?

Best Regards,

Louis

• ###### Re: How to set analysis in YTM with calculation

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

• ###### Re: How to set analysis in YTM with calculation

Thank you very much!

I will make it asap.

Many Thx!