If statement + Max function

[valnod_90]

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?

[Marco]

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)

[Vegar]

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]

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:

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

[tamilarasu]

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]

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]

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]

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