topic Re: How to make Table 2 Ratio LY display 0.235 when i select year = 2015 ? in QlikView

How to make Table 2 Ratio LY display 0.235 when i select year = 2015 ?

paulyeo11 — Fri, 25 Dec 2015 15:07:59 GMT

Hi Sunny

When i select 2015 Table 1 display 0.354

When i select 2014 Table 1 display 0.235

How to make Table 2 Ratio LY display 0.235 when i select year = 2015 ?

Re: How to make Table 2 Ratio LY display 0.235 when i select year = 2015 ?

sunny_talwar — Fri, 25 Dec 2015 22:44:18 GMT

This

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0,

RangeAvg(Only({<year = {$(=max(year)-1)}>}Aggr(Sum({<month, year>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year)),

sum({<month, year, YearMonth = {"$(=Dual(Date(YearEnd(AddYears(Monthstart(Max({<STK_CLOSE = {'*?'}>}YearMonth)), -1)), 'YY MMM'), MonthStart(YearEnd(AddYears(Max({<STK_CLOSE = {'*?'}>}YearMonth), -1)))))"}>}STK_CLOSE)))

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0, RangeSum(Above(Sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (COGS+COGS_INT_)/1), 0, RowNo())))

Re: How to make Table 2 Ratio LY display 0.235 when i select year = 2015 ?

paulyeo11 — Sat, 26 Dec 2015 00:49:15 GMT

Hi Sunny

wow , i am impress with your understanding of SET analysis. Thank you very much it finally work . By the way may i know you from which country ?

Paul

Re: How to make Table 2 Ratio LY display 0.235 when i select year = 2015 ?

sunny_talwar — Sat, 26 Dec 2015 00:59:35 GMT

‌Paul,

I am currently working in the US, but I am originally from India. What about you?

Re: How to make Table 2 Ratio LY display 0.235 when i select year = 2015 ?

paulyeo11 — Sat, 26 Dec 2015 02:07:04 GMT

Hi sunny

I am very respect the India mind. Very powerful. I am from singapore . Hope to see you one day.

Paul

Sent from my iPhone