Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Maybe like attached?
=Date(min( {<Item = {'B'}>} aggr(if( ItemDate > Min(total<ID> {<Item = {'A'}>}ItemDate), ItemDate),ID, ItemDate)))
=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))
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.
Maybe like attached?
=Date(min( {<Item = {'B'}>} aggr(if( ItemDate > Min(total<ID> {<Item = {'A'}>}ItemDate), ItemDate),ID, ItemDate)))
Thanks, that worked great!