3 Replies Latest reply: Jan 11, 2018 10:49 AM by micro win RSS

    Set Analysis - Bring Last Row (With Value <> Null)

    micro win

      Hello,

       

      I have the following table for a particular Client = 300 (note that dates have format DD/MM/YYYY):

       

      MALLCLIENTUNITCONTRACTCONTRACT_FROMCONTRACT_TOAREACAL_DATE
      MALL01300U00650025/01/201723/07/201719501/10/2017
      MALL01300U00850308/02/201809/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!!!

        • Re: Set Analysis - Bring Last Row (With Value <> Null)
          Sunny Talwar

          Not sure I follow the description... would you be able to share a sample to look at?

            • Re: Set Analysis - Bring Last Row (With Value <> Null)
              micro win

              Hello Sunny stalwar1,

               

              I send you the table with data:

               

              MALLCLIENTUNITCONTRACTCONTRACT_FROMCONTRACT_TOAREACAL_DATE
              MALL01300U00650025/01/201723/07/201719501/01/2017
              MALL01300U00650025/01/201723/07/201719501/01/2017
              MALL01300U00650025/01/201723/07/201719501/02/2017
              MALL01300U00650025/01/201723/07/201719501/02/2017
              MALL01300U00650025/01/201723/07/201719501/03/2017
              MALL01300U00650025/01/201723/07/201719501/03/2017
              MALL01300U00650025/01/201723/07/201719501/04/2017
              MALL01300U00650025/01/201723/07/201719501/05/2017
              MALL01300U00650025/01/201723/07/201719501/06/2017
              MALL01300U00650025/01/201723/07/201719501/07/2017
              MALL01300U00650025/01/201723/07/201719501/08/2017
              MALL01300U00650025/01/201723/07/201719501/09/2017
              MALL01300U00650025/01/201723/07/201719501/10/2017
              MALL01300U00850308/02/201809/03/2022-01/11/2017
              MALL02300U01080018/12/201417/12/201825001/01/2017
              MALL02300U01080018/12/201417/12/201825001/02/2017
              MALL02300U01080018/12/201417/12/201825001/03/2017
              MALL02300U01080018/12/201417/12/201825001/04/2017
              MALL02300U01080018/12/201417/12/201825001/05/2017
              MALL02300U01080018/12/201417/12/201825001/06/2017
              MALL02300U01080018/12/201417/12/201825001/07/2017
              MALL02300U01080018/12/201417/12/201825001/08/2017
              MALL02300U01080018/12/201417/12/201825001/09/2017
              MALL02300U01080018/12/201417/12/201825001/10/2017
              MALL02300U01080018/12/201417/12/201825001/11/2017
              MALL02300U01080018/12/201417/12/201825001/12/2017
              MALL03525U24090023/10/2015-21001/01/2017
              MALL03525U24090023/10/201501/01/201721001/02/2017
              MALL03525U24090023/10/201501/02/201721001/03/2017
              MALL03525U24090023/10/201501/03/201721001/04/2017
              MALL03525U24090023/10/201501/04/201721001/05/2017
              MALL03525U24090023/10/201501/05/201721001/06/2017
              MALL03525U24090023/10/201501/06/201721001/07/2017
              MALL03525U24090023/10/201501/07/201721001/08/2017
              MALL03525U24090023/10/201501/08/201721001/09/2017
              MALL03525U24090023/10/201501/09/201721001/10/2017
              MALL03525U24090023/10/201501/10/201721001/11/2017
              MALL03525U24090023/10/201501/11/201721001/12/2017
              MALL03525U240950---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):

               

              MALLCLIENTUNITCONTRACTCONTRACT_FROMCONTRACT_TOAREACAL_DATENotes
              MALL01300U006500 25/01/201723/07/201719501/10/2017Show this row
              MALL01300U00850308/02/201809/03/2022-01/11/2017No because Area is Null
              MALL02300U01080018/12/201417/12/201825001/12/2017Show this row
              MALL03525U24090023/10/201501/11/201721001/12/2017Show this row
              MALL03525U240950---01/12/2017No 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.

            • Re: Set Analysis - Bring Last Row (With Value <> Null)
              Anil Babu

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