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

Nested set analysis and date comparisons

I am trying to figure out the correct syntax to say "Find the minimum date for Item B which occurred after the minimum date for Item A".

For example, using the following table:

IDItemItemDate
1A1/1/2009
1B1/1/2010
2A1/1/2011
2B1/1/2010


I tried to use the following expression in a straight table, with ID as the dimension:

=min({<Item={'B'},ItemDate={">min({<Item={'A'}>} ItemDate)"}> ItemDate)


I am currently getting a null for both IDs, but I would expect it to say "1/1/2010" for ID 1 and null for ID 2

This needs to be done as a set analysis expression because I'm planning on using it for a more complicated query which has to be done this way.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like attached?

=Date(min( {<Item = {'B'}>} aggr(if( ItemDate > Min(total<ID> {<Item = {'A'}>}ItemDate), ItemDate),ID, ItemDate)))

View solution in original post

4 Replies
MK_QSL
MVP
MVP

=IF(Date(Min({<Item = {'B'}>}ItemDate))>Date(Min({<Item = {'A'}>}ItemDate)),Date(Min({<Item = {'B'}>}ItemDate)))

OR

=Date(Min({<Item = {'B'},ItemDate = {'>$(=Min({<Item = {'A'}>}ItemDate))'}>}ItemDate))

Not applicable
Author

Unfortunately neither of these work correctly. If we extend the table to contain these values:

Header 1Header 2Header 3
IDItemItemDate
1A1/1/2009
1B1/1/2010
1B1/1/2008
2A1/1/2011
2B1/1/2010
2B1/15/2010

We would expect to get a result of 1/1/2010 for ID 1, and nothing for ID 2.

The first option (using IF statements) gives nulls for both, because there is now a minimum date for B for both IDs that is before the first value of A.

The second option (using dollar expansion in set analysis) gives 1/1/2010 for both IDs. This is because the dollar expansion part returns 1/1/2009 regardless of the ID when used in a straight table with the dimension of ID.

swuehl
MVP
MVP

Maybe like attached?

=Date(min( {<Item = {'B'}>} aggr(if( ItemDate > Min(total<ID> {<Item = {'A'}>}ItemDate), ItemDate),ID, ItemDate)))

Not applicable
Author

Thanks, that worked great!