Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
JustinDallas
Not applicable

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?

1 Solution

Accepted Solutions
swuehl
Not applicable

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.

5 Replies
hector_munoz_in
Not applicable

Re: Ignore one fields selection, but process the others

Hi Justin,

Try the following:

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

Regards,
H

JustinDallas
Not applicable

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.

swuehl
Not applicable

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.

Not applicable

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

JustinDallas
Not applicable

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.