Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following table for a particular Client = 300 (note that dates have format DD/MM/YYYY):
MALL | CLIENT | UNIT | CONTRACT | CONTRACT_FROM | CONTRACT_TO | AREA | CAL_DATE |
---|---|---|---|---|---|---|---|
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/10/2017 |
MALL01 | 300 | U008 | 503 | 08/02/2018 | 09/03/2022 | - | 01/11/2017 |
I have Dimensions Mall, Client and Unit, and then all Expressions like Contract, Contract_From, Contract_To and Area.
The following Expression gets the last Contract for the last Date (Cal_Date) for each Mall/Client/Unit:
=MAX(IF(AGGR(NODISTINCT MAX(CAL_DATE),MALL)=CAL_DATE,CONTRACT))
It works fine, but the thing is that this case, for the last Cal_Date (01/11/2017) has Area = NULL and I'd need to modify the Expression in order to get those Contracts whose Area is not null for the last Cal_Date and Contract_From is not null too. In this case, I should get the first row of the table with Contract = 500.
Do you know how could I do that?
Thank you!!!
Not sure I follow the description... would you be able to share a sample to look at?
Perhaps this? But, Really not sure your problem
=MAX(IF(AGGR(NODISTINCT MAX(CAL_DATE),MALL)=CAL_DATE and Not IsNull(AGGR(NODISTINCT MAX(CAL_DATE),MALL))=CAL_DATE,CONTRACT))
Hello Sunny stalwar1,
I send you the table with data:
MALL | CLIENT | UNIT | CONTRACT | CONTRACT_FROM | CONTRACT_TO | AREA | CAL_DATE |
---|---|---|---|---|---|---|---|
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/01/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/01/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/02/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/02/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/03/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/03/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/04/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/05/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/06/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/07/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/08/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/09/2017 |
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/10/2017 |
MALL01 | 300 | U008 | 503 | 08/02/2018 | 09/03/2022 | - | 01/11/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/01/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/02/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/03/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/04/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/05/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/06/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/07/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/08/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/09/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/10/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/11/2017 |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/12/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | - | 210 | 01/01/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/01/2017 | 210 | 01/02/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/02/2017 | 210 | 01/03/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/03/2017 | 210 | 01/04/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/04/2017 | 210 | 01/05/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/05/2017 | 210 | 01/06/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/06/2017 | 210 | 01/07/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/07/2017 | 210 | 01/08/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/08/2017 | 210 | 01/09/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/09/2017 | 210 | 01/10/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/10/2017 | 210 | 01/11/2017 |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/11/2017 | 210 | 01/12/2017 |
MALL03 | 525 | U240 | 950 | - | - | - | 01/12/2017 |
I need to show a table with Dimensions Mall, Client and Unit with Expressions Contract, Contract_From, Contract_To and Area with the following data (rows in bold😞
MALL | CLIENT | UNIT | CONTRACT | CONTRACT_FROM | CONTRACT_TO | AREA | CAL_DATE | Notes |
---|---|---|---|---|---|---|---|---|
MALL01 | 300 | U006 | 500 | 25/01/2017 | 23/07/2017 | 195 | 01/10/2017 | Show this row |
MALL01 | 300 | U008 | 503 | 08/02/2018 | 09/03/2022 | - | 01/11/2017 | No because Area is Null |
MALL02 | 300 | U010 | 800 | 18/12/2014 | 17/12/2018 | 250 | 01/12/2017 | Show this row |
MALL03 | 525 | U240 | 900 | 23/10/2015 | 01/11/2017 | 210 | 01/12/2017 | Show this row |
MALL03 | 525 | U240 | 950 | - | - | - | 01/12/2017 | No because Contract and Area is Null |
The current Expressions I have are the following, for example to get the last Contract:
=MAX(IF(AGGR(NODISTINCT MAX(CAL_DATE),MALL)=CAL_DATE,CONTRACT))
** Note that I always select first 1 CLIENT from a Listbox in order to show that table **
Thanks.