Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
@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 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
Thank you, that worked!