Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

drillnaut
Valued Contributor

Miscalculation of Ranges

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.

RangeChart.png

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?

3 Replies
drillnaut
Valued Contributor

Re: Miscalculation of Ranges

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)

omarbensalem
Esteemed Contributor

Re: Miscalculation of Ranges

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:

=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

drillnaut
Valued Contributor

Re: Miscalculation of Ranges

Thanks Omar, especially since your broke it down logically.