Expression working intermittantly

[Anonymous]

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.

[petter]

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.

[Anonymous]

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)

[petter]

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.

[sunny_talwar · Accepted solution]

Can you try this

If(Aggr(NODISTINCT Max(RecordEffectiveFromDate), NSN) = RecordEffectiveFromDate, 'Y', Null())

[Anonymous]

Yes I have checked that the time stamps are the same too and I get the same result when the time stamp is included.

[Anonymous]

I will try that.

[Anonymous]

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