Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
MVP
MVP

Re: Re: Re: Nested set analysis and date comparisons

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
MVP
MVP

Re: Nested set analysis and date comparisons

=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

Re: Re: Nested set analysis and date comparisons

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.

MVP
MVP

Re: Re: Re: Nested set analysis and date comparisons

Maybe like attached?

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

View solution in original post

Not applicable

Re: Re: Re: Nested set analysis and date comparisons

Thanks, that worked great!