Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
valnod_90
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?

7 Replies
Partner
Partner

Re: If statement + Max function

Hi,

if you have a table like:

YearJobs
2016A
2017B
2018C

 

and you want as result:

YearJobs
2018C

 

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

Partner
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
???
valnod_90
New Contributor III

Re: If statement + Max function

Hi,

 

I tried adjusting your function like this:

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

but it does not work.


My table is something like this:

JobModelOrder Date
CS4512X15/05/2018
DF5898Y26/07/2017
GV45687Z08/09/2016

 

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

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
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
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
???
valnod_90
New Contributor III

Re: If statement + Max function

 

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


Maybe because I have more rows for each year?

Like this:

 

JobModelOrder Date
CS4512X15/05/2018
FG8954D12/08/2018
DV23589V10/10/2018
AZ8955V12/11/2018
DF5898Y26/07/2017
JL89657X10/10/2017
GV45687Z08/09/2016
FG6525D12/12/2016