Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
valnod_90
Contributor III
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
Marco
Partner - Contributor III
Partner - Contributor III

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)

Vegar
MVP
MVP

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)
valnod_90
Contributor III
Contributor III
Author

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

tamilarasu
Champion
Champion

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). 

Marco
Partner - Contributor III
Partner - Contributor III

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)

Vegar
MVP
MVP

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)
valnod_90
Contributor III
Contributor III
Author

 

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