Ignore one fields selection, but process the others

[JustinDallas]

Hello Everyone,

I have a dashboard where I am using the YOY construct, i.e Sum({$<Year = {$(=max(Year)-1)}>} [Sales]) vs Sum({$<Year = {$(=max(Year)}>} [Sales]).  My problem is that when a user selects Year='2015', the years that satisfy the {Max(Year)-1} clause are excluded.  So to solve that, I've added the '1' to my analysis giving me this.

Sum({1<Year = {$(=max(Year)-1)}>} [Sales])

The only problem is that the '1' causes ALL selections to be ignored.  So when the user selects Quarter=1 or Month=January, it doesn't cascade through.

How do I make my analysis ignore the year selection but use the calculation, and then apply any other user selections?

[hector_munoz]

Hi Justin,

Try the following:

Sum({<Year = {$(=max({1}Year)-1)}>} [Sales])

Regards,
H

[JustinDallas]

When I try that, I still get a sum of "0".  If I'm reading that correctly, that statement says "Where year equals 'the maximum year' regardless of what the user has selected".  But then when it goes to find records that match the year, it sees they have been excluded by the user selecting a 'Year' value.

In other words, that statement will always return '2016' assuming 2017 is the latest data.  But I'm not sure, this Qlik Sense thing is wearing me out.

[swuehl · Accepted solution]

Justin, these kind of set expressions should override the user selections in Year field, with the value determined by the set modifier. Are you in fact selecting values in a field different from 'Year', e.g. FinancialYear or something like that? That could easily lead to a conflicting selection.

[#5]

Will respect user selection

sum( {$} SALES) 

Set Analysis will ignore Year selection but will abide by Month selection:

sum({<FISCAL_YEAR =, FISCAL_MONTH={$(=FISCAL_MONTH)} >}  SALES)

Set Analysis will ignore Year selection but will abide by Month selection:

sum({<FISCAL_YEAR = { $(=max(FISCAL_YEAR)-1) }, FISCAL_MONTH={$(=FISCAL_MONTH)} >}  SALES)

Set Analysis will always show year = 2016 if max year is 2017

sum({<FISCAL_YEAR ={$(=max(FISCAL_YEAR) - 1)}, FISCAL_MONTH={$(=FISCAL_MONTH)} >}SALES)

Hope this helps.

Lyndon

[JustinDallas]

Honestly Stefan, I'm not sure what happened.  My "Year" list had entirely too many values. so I changed my filter list so that the dimension was .

if( Year > 2010, Year, Null() )

When I REMOVED that FilterList and just used a regular FilterList, everything worked.

Thanks for tolerating my blistering incompetence.