Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
brentski
Contributor III
Contributor III

Unexpected behavior of If and Max statement

I am back with another question.  This one boggles my mind.

 

I have a nested If statement that works like this

if([Incurred Year]=2020, do a calculation

, if([Incurred Year]=2019, do a different calculation

, if([Incurred Year]=2018, do a third calc

When I hard code the values the expression completes without incidence.  However when I do this:

if([Incurred Year]=max([Incurred Year]), do a calculation

, if([Incurred Year]=2019, do a different calculation

, if([Incurred Year]=2018, do a third calc

The expression only calculates for 2020 and returns no values for 2019 or 2018.

 

What I found I have to do is:

if([Incurred Year]=$(=Max([Incurred Year]))

 

1. Any idea why it functions this way?  

2. What is the difference between Max([Incurred Year]) and 

$(=Max([Incurred Year]))?

 

 

Labels (3)
1 Solution

Accepted Solutions
rubenmarin

Hi @brentski, if this is on a table or other chart Max() will be calcualted for the current dimension, so if dimension has 2019 as Year, 2019 will be the Max year, and the bar with 2018 as Year will have 2018 as Max Year.

$() is expanded before creating the dimensions, so it has the value calculated ignoring dimensions.

Another way to ignore dimensions is using the TOTAL qualifier like: if([Incurred Year]=max(TOTAL [Incurred Year])

View solution in original post

2 Replies
rubenmarin

Hi @brentski, if this is on a table or other chart Max() will be calcualted for the current dimension, so if dimension has 2019 as Year, 2019 will be the Max year, and the bar with 2018 as Year will have 2018 as Max Year.

$() is expanded before creating the dimensions, so it has the value calculated ignoring dimensions.

Another way to ignore dimensions is using the TOTAL qualifier like: if([Incurred Year]=max(TOTAL [Incurred Year])

brentski
Contributor III
Contributor III
Author

That makes so much sense.  And yes you were correct that I had Year as a dimension which caused the behavior.  Thank you for explaining this fundamental behavior of Qlik.