Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

View solution in original post

2 Replies
sunny_talwar

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
Master
Master
Author

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 ?