# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor III

## If statement + Max function

Hi everyone,

I would like to learn how to put together an if statement with a "max" expression in qlik sense.
For example: I have many years together and I want to see only the latest works using a max function, in a parametric way
I mean something like this: if (year = max year, Jobs)
I have to understand how to write it correctly -> year = max year

Is there a way to do it?

Labels (3)

7 Replies
Partner

## Re: If statement + Max function

Hi,

if you have a table like:

 Year Jobs 2016 A 2017 B 2018 C

and you want as result:

 Year Jobs 2018 C

You can write this expression: only({<Year={"\$(=Max(Year))"}>}Jobs)

Partner

## Re: If statement + Max function

The alternative solution provided by @Marco is quite good, check it out.

A correct syntax for what you are trying to accomplish is:
IF( year=MAX(year), Jobs)
???
Celebrating my 300 accepted solution in the Qlik Community
???
New Contributor III

## Re: If statement + Max function

Hi,

only({<year([Orde Date])={"\$(=Max(year([Order Date]))"}>}Job)

but it does not work.

My table is something like this:

 Job Model Order Date CS4512 X 15/05/2018 DF5898 Y 26/07/2017 GV45687 Z 08/09/2016

and I have to filter the year of the "Order Date" field

MVP

## Re: If statement + Max function

Hello,

In that case, you can try this,

only({<[Order Date1]={">=\$(=YearStart(Max([Order Date])))"}>}Job)

If the document date format is different from your field format then try,

only({<[Order Date1]={">=\$(=Date(YearStart(Max([Order Date])),'DD/MM/YYYY'))"}>}Job)

Change the highlighted format as per your field (if required).

Partner

## Re: If statement + Max function

To simplify the set analisys,  I suggest to calculate in script a new field:

Year([Orde Date]) as [Year Order Date]

Then use the new field in set analisys:

only({<[Year Orde Date])={"\$(=Max([Year  Order Date])"}>}Job)

Partner

## Re: If statement + Max function

You can look at Set-modifiers as a way to do invisible selections for your aggregation function (you're using only() as your aggregation function). You can only make selections in a field not in a expression, hence you can not modify year([Order Date]), you will need to adjust your set to modify the field [Order Date].

I notice that @Marco also have answered. Consider his advice on adjusting the script to get an easier handling of expressions.

If you stick with the current datamodell then you will probably get to extend Marcos last suggestion with an upper bound as well. It will look something like this:

{">=\$(=Date(YearStart(Max([Order Date])),'DD/MM/YYYY')) <=\$(=Date(YearEnd(Max([Order Date])),'DD/MM/YYYY')) "}>}Job)
???
Celebrating my 300 accepted solution in the Qlik Community
???
New Contributor III

## Re: If statement + Max function

I tried all these functions but they do not work, because Qlik says "invalid size"

Maybe because I have more rows for each year?

Like this:

 Job Model Order Date CS4512 X 15/05/2018 FG8954 D 12/08/2018 DV23589 V 10/10/2018 AZ8955 V 12/11/2018 DF5898 Y 26/07/2017 JL89657 X 10/10/2017 GV45687 Z 08/09/2016 FG6525 D 12/12/2016