Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Not applicable

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

Hi Sunny

How to transform Table 1 into Table 2 ?

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

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

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

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

Capture.PNG

2 Replies
sunny_talwar
Not applicable

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

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

Capture.PNG

paulyeo11
Not applicable

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

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 ?