How to make my Table 2 Display RATIO = 0.354 , Now it display 1.297

[paulyeo11]

Hi Sunny

How to transform Table 1 into Table 2 ?

And i need Table 2 Display RATIO = 0.354 , Now it display 1.297

[sunny_talwar · Accepted solution]

Not sure how this will behave with change in selections, but with current selections, this expression seems to work for STK_AVG:

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

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

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

[paulyeo11]

Hi Sunny

Great it work again.

Now i need your help again to get the LY ratio :-

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