Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
stsk
Contributor
Contributor

Trying to use an AGGR(Max date) with same date in IF expression

I'm trying to create the following expression using an LOD max-min and a non LOD subtraction on the same date.  When I use the non LOD updated_dt in the if the LOD function behaves differently.  Below is the expression I'm using and the data that is being returned.  Since value = UNPLANNED and <> 1 then the AGGR part of the if should return but it is returning blank, I've put the AGGR expression as a separate field and it works fine.  And when I take the "-updated_dt" from the true part of the expression it also works fine.  

Expression:

= IF(value = 'UNPLANNED' AND AGGR(COUNT(id + code + year), id + code + year) = 1
, Today() - updated_dt
, AGGR(MAX(updated_dt), id, code, year) - AGGR(MIN(updated_dt), id, code, year)

)

Using Expression above

ValueCountExpression
UNPLANNED4-

 

if I leave off the updated_dt on the 1st in the TRUE statement of the IF, the AGGR returns as expected.  

= IF(value = 'UNPLANNED' AND AGGR(COUNT(id + code + year), id + code + year) = 1
, Today()
, AGGR(MAX(updated_dt), id, code, year) - AGGR(MIN(updated_dt), id, code, year)

)

ValueCountExpression
UNPLANNED442

 

Is there something I am missing?  Can anyone help?

1 Solution

Accepted Solutions
Kushal_Chawda

@stsk  You might have multiple lines for updated_dt for combination of dimension, so you may need to use aggregate function to return single value for combination of dimensions used in aggr.  May be try below

= IF(value = 'UNPLANNED' AND AGGR(COUNT(id + code + year), id + code + year) = 1
, Today() - AGGR(MAX(updated_dt), id, code, year)
, AGGR(MAX(updated_dt), id, code, year) - AGGR(MIN(updated_dt), id, code, year)

)

You can either use Min or Max in highlighted part based on your requirement

View solution in original post

2 Replies
Kushal_Chawda

@stsk  You might have multiple lines for updated_dt for combination of dimension, so you may need to use aggregate function to return single value for combination of dimensions used in aggr.  May be try below

= IF(value = 'UNPLANNED' AND AGGR(COUNT(id + code + year), id + code + year) = 1
, Today() - AGGR(MAX(updated_dt), id, code, year)
, AGGR(MAX(updated_dt), id, code, year) - AGGR(MIN(updated_dt), id, code, year)

)

You can either use Min or Max in highlighted part based on your requirement

stsk
Contributor
Contributor
Author

Thank you, that worked!