topic Re: Re: Re: Nested set analysis and date comparisons in QlikView

Nested set analysis and date comparisons

Sun, 16 Mar 2014 04:44:09 GMT

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:

ID	Item	ItemDate
1	A	1/1/2009
1	B	1/1/2010
2	A	1/1/2011
2	B	1/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.

Re: Nested set analysis and date comparisons

MK_QSL — Sun, 16 Mar 2014 08:33:39 GMT

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

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

Re: Re: Nested set analysis and date comparisons

Sun, 16 Mar 2014 22:57:37 GMT

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

Header 1	Header 2	Header 3
ID	Item	ItemDate
1	A	1/1/2009
1	B	1/1/2010
1	B	1/1/2008
2	A	1/1/2011
2	B	1/1/2010
2	B	1/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.

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

swuehl — Sun, 16 Mar 2014 23:19:57 GMT

Maybe like attached?

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

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

Mon, 17 Mar 2014 00:11:45 GMT

Thanks, that worked great!