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.
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:
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.