3 Replies Latest reply: Dec 12, 2017 10:04 AM by Justin Dallas RSS

    Miscalculation of Ranges

    Justin Dallas

      Hello Everyone,


      I have a table where I am looking at employment and termination ranges.  Right now, I have this measure


      COUNT( {<[Employee Start Date]={"<=$(Min(GenDate))"},
                  mpp_terminationdt={">=$(Min(GenDate))<=$(Max(GenDate))"}>} mpp_id)


      What I am trying to say is:

          Count the distinct mpp_ids, where the [Employee Start Date] is LESS THAN  the minimum GenDate, and the mpp_terminationdt is either GREATER Than the minimum GenDate or LESS THAN the maximum GenDate.  A picture of an employment that would satifsy this constraint would look like this.




      Unfortunately, when I select the year 2017, I end up with employees being brought back that were hired and termed in 2016.  Does anyone see anything wrong with my expression or could it be something more sinister?

        • Re: Miscalculation of Ranges
          Justin Dallas

          The issue was that I did not have an equals sign BEFORE the Min/Max in the mpp_terminationdt clause.



          COUNT( {<[Employee Start Date]={"<=$(Min(GenDate))"},
                      mpp_terminationdt={">=$(=Min(GenDate)) <=$(=Max(GenDate))"}>} mpp_id)
            • Re: Miscalculation of Ranges
              omar bensalem

              Please refer to this thread in which i tried to explain some of the time expressions step by step :

              YTD, MTD issue


              part of what I explained is what what u were dealing with (the missing = sign)


              Let explain this : date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}


              We want to work from date=01/01/2016 to the selected date=12/04/2016 right?

              So we're working with the field :

              a) date={    }

              b) Now we wanna this date to be <=selected date which is max(date) ;

              max(date) is a function so it needs an "=" sign:


              when we have a '=' we add the $ (before each calculation) : $(=max(date) ) => this is 12/04/2016

              Now we add the <= so we'll have :  <=$(=max(date) )

              Hope this would help u