topic Re: Trying to use an AGGR(Max date) with same date in IF expression in App Development

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

stsk — Mon, 30 Aug 2021 14:36:32 GMT

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

Value	Count	Expression
UNPLANNED	4	-

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)

)

Value	Count	Expression
UNPLANNED	4	42

Is there something I am missing? Can anyone help?

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

Kushal_Chawda — Mon, 30 Aug 2021 14:48:45 GMT

@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

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

stsk — Tue, 31 Aug 2021 15:06:54 GMT

Thank you, that worked!