Unexpected behavior of If and Max statement

[brentski]

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]))?

[rubenmarin · Accepted solution]

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]

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.