Dynamic Max Date in Set Analysis

[#1]

Hi everyone,

I have a tricky requirement where I need help.

In my application I have different ReferenceNumbers. Each number is associated with different dates. Each date is associated with one or more records.

What I like to achieve: For each ReferenceNr the Max.Date should be selected. If one of the Records associated with this Max.Date is NULL, the result should be 0 otherwise 1. Besides, only those Records which are in Group 'A' should be included.

I tried something like this:

=if( len(concat( {$<Group={'A'}, [Date]={"=date( max(Date), 'yyyyMMdd' )"} >}  trim(Record))) >0, 1, 0)

or this:

=if( len(concat( {$<Group={'A'}, [Date]={"=date(max(aggr(max({<Group={'A'}>}Date), ReferenzNr)), 'yyyyMMdd' )"} >}  trim(Record))) >0

  , 1, 0)

But it doesn't work. I have to select ReferenceNr and the corresponding Max.Date in order to get the right result. (-->ExampleApp)

Is there a way to get the maximum date for each ReferenceNr in a Set Analysis?

I would really appreciate your help!

Thanks,

Lisa

[sunny_talwar · Accepted solution]

Try this expression:

=If(Len(Trim(FirstSortedValue(Aggr(MinString(Record), ReferenzNr, Group, Date), -Aggr(Date, ReferenzNr, Group, Date)))) = 0, 0, 1)

[Anonymous]

Hi Lisa,

See this document, it will help you so much:

https://community.qlik.com/docs/DOC-4249

Regards!

[#4]

That worked... Thanks a lot!!!