4 Replies Latest reply: Mar 16, 2014 8:11 PM by Joseph Dalton

# 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.

• ###### 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))

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

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

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.

• ###### 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)))

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

Thanks, that worked great!