Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.Capture.PNG

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
sunny_talwar

Can you try this

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

View solution in original post

7 Replies
petter
Partner - Champion III
Partner - Champion III

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
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Can you try this

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

Anonymous
Not applicable
Author

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
Not applicable
Author

I will try that.

Anonymous
Not applicable
Author

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