Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
Master

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

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 ?

1 Solution

Accepted Solutions
MVP

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

4 Replies
MVP

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

Master
Author

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

MVP

‌Paul,

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

Master
Author

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

Community Browser