Dynamic Max Date in Set Analysis

Wed, 31 Aug 2016 10:02:43 GMT

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

Re: Dynamic Max Date in Set Analysis

sunny_talwar — Wed, 31 Aug 2016 10:24:41 GMT

Try this expression:

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

Re: Dynamic Max Date in Set Analysis

Anonymous — Wed, 31 Aug 2016 12:00:45 GMT

Hi Lisa,

See this document, it will help you so much:

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

Regards!

Re: Dynamic Max Date in Set Analysis

Wed, 31 Aug 2016 12:05:59 GMT

That worked... Thanks a lot!!!

topic Dynamic Max Date in Set Analysis in QlikView

Dynamic Max Date in Set Analysis

Re: Dynamic Max Date in Set Analysis

Re: Dynamic Max Date in Set Analysis

Re: Dynamic Max Date in Set Analysis