Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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
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
Hi Kush
Thank you it work fine.
Paul
that's great
hI kUSH
thank you it work
paul