7 Replies Latest reply: Jun 12, 2018 6:41 AM by Avinash R RSS

    Null Condition Set Analysis

    Bashar Khalid

      Hi,

       

      In the below expression of a master measure, I want to set the condition to Separation Date is either equal or higher to the variable OR is Null.

       

      Count({<[Seperation Date]= {">=$(=date($(vMinDateInHiresPeriod)))", isNull()}>}Distinct [Employee ID])


      The above is basically what I want, but its not working obviously.


      The variable part works fine, but adding the null condition is the challenge for me.

       

      Thanks

        • Re: Null Condition Set Analysis
          omar bensalem

          Maybe:

           

          Count({

          <[Seperation Date]= {">=$(=date($(vMinDateInHiresPeriod)))"}>

          +

          <[Seperation Date]={"=isNull([Seperation Date])=-1"}>


          }Distinct [Employee ID])


            • Re: Null Condition Set Analysis
              Bashar Khalid

              What if the there were more conditions in the set analysis, like so?

               

              Count({<[Date]={"<=$(=date($(vMinDateInHiresPeriod)))"},

              [Seperation Date]= {">=$(=date($(vMinDateInHiresPeriod)))"} + [Seperation Date]={"=isNull([Seperation Date])=-1"},

              Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}>}Distinct [Employee ID])

               

              I tried implementing your answer here, but it didnt work.

                • Re: Null Condition Set Analysis
                  omar bensalem

                  "+" is like "or"; so, it would sthing like:

                   

                  Count({

                  <

                  [Date]={"<=$(=date($(vMinDateInHiresPeriod)))"},

                  [Seperation Date]= {">=$(=date($(vMinDateInHiresPeriod)))"},

                  Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

                  >


                  +


                  <

                  [Date]={"<=$(=date($(vMinDateInHiresPeriod)))"},

                  [Seperation Date]={"=isNull([Seperation Date])=-1"},

                  Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

                  >

                   

                  }Distinct [Employee ID])

              • Re: Null Condition Set Analysis
                Devyanshu Gupta

                Hi,

                When checking null values in set, isnull function works without the brackets().

                So try

                {<Date={isnull}>} in your set.

                I've used this for not equals to it works for that.

                 

                Regards,

                Devyanshu

                • Re: Null Condition Set Analysis
                  Avinash R

                  Does you date contains any space or tab space ??

                    • Re: Null Condition Set Analysis
                      omar bensalem

                      Good remark, since the "definition" of null values could differ...

                      maybe len(trim(Date))=0 could handle it better..

                       

                      Just add it then:

                       

                      Count({

                      <

                      [Date]={"<=$(=date($(vMinDateInHiresPeriod)))"},

                      [Seperation Date]= {">=$(=date($(vMinDateInHiresPeriod)))"},

                      Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

                      >


                      +


                      <

                      [Date]={"<=$(=date($(vMinDateInHiresPeriod)))"},

                      [Seperation Date]={"=isNull([Seperation Date])=-1"},

                      Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

                      >

                       

                       

                      +


                      <

                      [Date]={"<=$(=date($(vMinDateInHiresPeriod)))"},

                      [Seperation Date]={"=len(trim([Seperation Date]))=0"},

                      Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

                      >

                      }Distinct [Employee ID])

                       

                       

                       

                      and please try this; I think it would also work:

                       

                      count({

                      <

                      [Date]={"<=$(=date($(vMinDateInHiresPeriod)))"},

                      [Seperation Date]= {">=$(=date($(vMinDateInHiresPeriod)))   OR (=isNull([Seperation Date])=-1) OR (=len(trim([Seperation Date]))=0)"},

                      Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

                      >}Distinct [Employee ID])