Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

joao_ben
New Contributor

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?

Thanks in advance

Tags (1)
10 Replies
rittermd
Honored Contributor

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. 

kkkumar82
Valued Contributor III

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

joao_ben
New Contributor

Re: Searching in Set Analysis

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

joao_ben
New Contributor

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

rohitraut
Contributor

Re: Searching in Set Analysis

Hello Joao,

Try this,

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

Hope this helps!

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?

joao_ben
New Contributor

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

joao_ben
New Contributor

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?