topic Re: How to make RATIO_CY1 display as 0.389 , now display as 0.214 ? in QlikView

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

paulyeo11 — Sat, 26 Dec 2015 02:02:14 GMT

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

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

sunny_talwar — Sat, 26 Dec 2015 03:30:49 GMT

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?

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

paulyeo11 — Sat, 26 Dec 2015 06:27:28 GMT

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

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

Kushal_Chawda — Sat, 26 Dec 2015 08:02:13 GMT

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

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

paulyeo11 — Sat, 26 Dec 2015 13:57:08 GMT

Hi Kush

Thank you it work fine.

Paul

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

Kushal_Chawda — Sat, 26 Dec 2015 14:06:24 GMT

that's great

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

paulyeo11 — Sat, 26 Dec 2015 14:49:14 GMT

hI kUSH

thank you it work

paul