10 Replies Latest reply: Sep 11, 2017 3:42 AM by Joao Bernardo

Searching in Set Analysis

Hey Guys (First time poster here)

I'm very new to sense, and I've been following an ancient book (QlikSense CookBook), and they give this expression as a measure:

sum({<Year={">=\$(=MAX(Year)-1)<=\$(=MAX(Year))"}>}Sales)

But the returned values in the chart include more years than the 2 selected...

Am I doing something wrong?

Is there a simpler way to do this?

• Re: Searching in Set Analysis

This expression is looking at greater than or equal to the MAX Year - 1 and less than or equal to the Max Year if the syntax is all correct.

If it is not returning what you expected then I would double check the syntax and make sure you have all of the brackets, commas, parenthesis, etc. in the right combination.

You could use an If statement and then you don't have to worry about all of the syntax in Set Analysis.

• Re: Searching in Set Analysis

I can't see where I went wrong... I'm gonna review the load script...

If not I'm gonna use an if statement.. Thank you

• Re: Searching in Set Analysis

Check if you have included space here highlighted in Red

sum({<Year={">=\$(=MAX(Year)-1) <= \$(=MAX(Year))"}>}Sales)

if you have written space after "<=" remove that

• Re: Searching in Set Analysis

I don't have any spaces after that... But thanks anyway

• Re: Searching in Set Analysis

Hello Joao,

Try this,

Hope this helps!

• Re: Searching in Set Analysis

I don't have the computer with qlik sense right now, but monday morning i'll try that first thing.

Thanks

• Re: Searching in Set Analysis

Hi Joao, the expression seems ok... the dimension of the chart is the field 'Year'? There is another expression in the chart?

• Re: Searching in Set Analysis

Yes, the dimension is 'Year'. And there isn't.

I'm losing my mind.

• Re: Searching in Set Analysis

I would try to add a table with Year as dimension and different expression to check relationships in the data:

- sum(Sales) // check relationship between year and sales

- MAX(Year) // confirm Year is numeric and Max works

- sum({<Year={"\$(=MAX(Year))"}>}Sales) // only max year should have data

- sum({<Year={"\$(=MAX(Year)-1)"}>}Sales) // only max-1 has data

If not, can you upload a sample to take a look?

• Re: Searching in Set Analysis

Thank you so much. Everything was correct, but apparently there was an option that was activated that enabled showing null values... When i disabled it everything was all right!!