5 Replies Latest reply: Apr 17, 2017 5:08 PM by Justin Dallas

# Ignore one fields selection, but process the others

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?

• ###### Re: Ignore one fields selection, but process the others

Hi Justin,

Try the following:

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

Regards,
H

• ###### Re: Ignore one fields selection, but process the others

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.

• ###### Re: Ignore one fields selection, but process the others

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.

• ###### Re: Ignore one fields selection, but process the others

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.

• ###### Re: Ignore one fields selection, but process the others

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