How to make Table 2 Ratio LY display 0.235 when i select year = 2015 ?

[paulyeo11]

Hi Sunny

When i select 2015 Table 1 display 0.354

When i select 2014 Table 1 display 0.235

How to make Table 2 Ratio LY display 0.235 when i select year = 2015 ?

[sunny_talwar · Accepted solution]

This

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0,

RangeAvg(Only({<year = {$(=max(year)-1)}>}Aggr(Sum({<month, year>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year)),

sum({<month, year, YearMonth = {"$(=Dual(Date(YearEnd(AddYears(Monthstart(Max({<STK_CLOSE = {'*?'}>}YearMonth)), -1)), 'YY MMM'), MonthStart(YearEnd(AddYears(Max({<STK_CLOSE = {'*?'}>}YearMonth), -1)))))"}>}STK_CLOSE)))

/

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0, RangeSum(Above(Sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo())))

[paulyeo11]

Hi Sunny

wow , i am impress with your understanding of SET analysis. Thank you very much it finally work . By the way may i know you from which country ?

Paul

[sunny_talwar]

‌Paul,

I am currently working in the US, but I am originally from India. What about you?

[paulyeo11]

Hi sunny

I am very respect the India mind. Very powerful. I am from singapore . Hope to see you one day.

Paul

Sent from my iPhone