# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for
Did you mean:
Highlighted
Specialist

## Expression working intermittantly

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.

1 Solution

Accepted Solutions
Highlighted
MVP

Can you try this

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

7 Replies
Highlighted
MVP

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.

Specialist

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)

Highlighted
MVP

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.

Highlighted
MVP

Can you try this

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

Highlighted
Specialist

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

Highlighted
Specialist

I will try that.

Highlighted
Specialist

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.