QlikView possible set analysis of dynamic table

[cjzimm76]

All,

I am trying to create a table where I want to show the name of an applicant, the application date and the application type.  The twist is I only want to show the applicant 1 time even if they have submitted multiple applications.  If they have more than 1 application, then show the first application date and the Type of that first application.  And the UI has 2 input boxes to create a dynamic date range for the application date.

The data structure of the table is pretty simple:

NameID, Name, ApplicationID, ApplicationDate, ApplicationType

I am creating a straight table with Name as a dimension.  And I can get the first application date for the expression:

=MinString({<AppDate = {'>=$(vStartDate)<=$(vEndDate)'}>} AppDate)

But how do I get the Type if the applicant has more than 1 application within that date range?

Charlie

[abeyphilip]

Hi,

See if this helps.

Regards,

Abey

[cjzimm76]

Not quite, but close.  I don't want the max App Type.  But the App Type of the app with the Max App Date.  Could that be done with a nested aggr?

[cjzimm76]

I thought I almost had it using the expression

FirstSortedValue (ApplicationType, aggr(max(ApplicationDate),Name))

But that didn't work.

[Gysbert_Wassenaar · Accepted solution]

See attached example. Is that what you were trying to get?