Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an expression which compares two fields and returns a Y or a null, it is working on some lines but not on others. I have confirmed that the two things I am comparing are the same.
This is the expression, if((aggr(max(RecordEffectiveFromDate),NSN))=(RecordEffectiveFromDate),'Y',Null())
I have tried it with date() and date#() and I get the same result.
Any ideas on what to try would be appreciated.
Can you try this
If(Aggr(NODISTINCT Max(RecordEffectiveFromDate), NSN) = RecordEffectiveFromDate, 'Y', Null())
The Aggr function is not an aggregation function that returns a single value or null. It is an advanced aggregation function and it might return null, one value but also two or more values. The last case will create a problem for you if statement and this is most likely what is happening.
Normally the Aggr function should be wrapped in a regular aggreation function like Sum, Count or one of the other aggregation functions available.
No the aggr function returns a date and then the if compares that date to another date
aggr function - (aggr(max(RecordEffectiveFromDate),NSN)
date it is comparing to (RecordEffectiveFromDate)
The two fields that displays as date could have a time part that is not shown. Are you sure that the time part has been removed so they are truly pure dates without a time?
What happens if you do this:
If(( Floor(Aggr(Max(RecordEffectiveFromDate),NSN))=Floor(RecordEffectiveFromDate),'Y',Null())
The Floor function turns the decimal part of the underlying number representation into 0 - truncates it... This essentially forces each datetime to become pure dates before comparing them.
Can you try this
If(Aggr(NODISTINCT Max(RecordEffectiveFromDate), NSN) = RecordEffectiveFromDate, 'Y', Null())
Yes I have checked that the time stamps are the same too and I get the same result when the time stamp is included.
I will try that.
Thank you all for your comments, I have conceded defeat on the report I was working as I don't have all the data I need.
I will mark the thread as assumed answered