Searching in Set Analysis

[joao_ben]

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

[rittermd]

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]

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]

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

[joao_ben]

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]

Hello Joao,

Try this,

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

Hope this helps!

[rubenmarin]

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

[joao_ben]

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

Thanks

[joao_ben]

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

I'm losing my mind.

[rubenmarin]

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?