Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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())))
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())))
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
Paul,
I am currently working in the US, but I am originally from India. What about you?
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