Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
Please refer to this thread in which i tried to explain some of the time expressions step by step :
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:
=max(date)
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
Thanks Omar, especially since your broke it down logically.