Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

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

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

1 Solution

Accepted Solutions

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

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



6 Replies

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

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
Valued Contributor II

Re: How to make RATIO_CY1 display as 0.389 , now display as 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

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

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
Valued Contributor II

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

Hi Kush

Thank you it work fine.

Paul

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

that's great

paulyeo11
Valued Contributor II

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

hI kUSH

thank you it work

paul

Community Browser