How to make RATIO_CY1 display as 0.389 , now display as 0.214 ?

[paulyeo11]

Hi Sunny

I have make use of your expression from RATIO_LY , which is correct and i convert to RATIO_CY1.

Expression at RATIO_CY1 is wrong , and RATIO_CY2 is correct , the value 0.389 is what i want. 

Enclosed my QV Doc

Paul

[sunny_talwar]

Not sure I understand your calculation for RATIO_CY1. In addition when I opened the application, I saw its value as 0.321 instead of 0,214 which you mentioned. What selections did you make to get 0.214?

[paulyeo11]

Hi Sunny

If you select year = 2015 and month =6 , you will notice RATION_CY1. Which is SET analysis the expression I did got problem , as for RATIO_CY2 which is using column(1)/column(2) which return the right value.

You did for the LY Ratio using SET ANALYSIS is working fine. Now I need to use SET analysis for RATION CY

Paul

Sent from my iPhone

[Kushal_Chawda · Accepted solution]

Use below expression

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

RangeSum(Above(Sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo())))

It's working fine for me

[paulyeo11]

Hi Kush

Thank you it work fine.

Paul

[Kushal_Chawda]

that's great

[paulyeo11]

hI kUSH

thank you it work

paul