topic Between Date Not working correctly in QlikView

Between Date Not working correctly

Wed, 22 Dec 2010 02:21:35 GMT

Sample code:

=Count(if(datHired >= AddYears(DateFrom,-2) AND datHired <= AddYears(DateTo,-2) ,lngEmployeeID))

The above works perfectly unless the datHired is equal to the "To" Date (AddYears(DateTo,-2) )

Example:

If the To: date is '1/31'2008' the EmployeeID is excluded from the COUNT. The date is formatted straight from SQL as a DATE datatype, so there is no time confusion.

This is driving me nuts! Any and all help is greatly appreciated.

Thanks in advance!

Between Date Not working correctly

pover — Wed, 22 Dec 2010 02:41:10 GMT

Just to make sure, do a num(addyears(DateTo,-2)) to make sure you get a whole number.

On a side note, I never knew the addyears() function existed. I've always used addmonths() because addyears() doesn't appear in the list of date and time functions in the help file. I wonder what others functions exist out there that aren't documented....

Regards.,

Between Date Not working correctly

Wed, 22 Dec 2010 16:29:40 GMT

Thanks, Karl!

Ran the number conversion and a whole number is returned as expected. Going to keep cranking away at this until I find a solution.

Oh and the AddYears function is only available in QV10 . . I believe.

Between Date Not working correctly

pover — Wed, 22 Dec 2010 16:36:27 GMT

Bummer. It's not that easy then.

I would create a straight table with IngEmployeeID as the dimension and AddYear(DateFrom,-2),AddYear(DateTo,-2), dateHired and the original count as expressions and see if you find anything strange.

Good luck.

Between Date Not working correctly

jonathandienst — Wed, 22 Dec 2010 16:39:21 GMT

I think that Num only changes the display of the number, and not the internal representation. So it looks like a whole number, but in fact it still has a fractional portion.

To get a true whole number, use one of the round, floor or ceil functions, depending on how you want to truncate the date value.

Jonathan

Between Date Not working correctly

jonathandienst — Wed, 22 Dec 2010 16:45:42 GMT

FWIW AddYears is available in QV9 (at least in SR5), but never documented!

Between Date Not working correctly

Wed, 22 Dec 2010 17:18:49 GMT

Turns out the Between process was functioning correctly, but I was excluding the data from the dimension.

Thanks for all of your help guys! Now can someone help me get this egg off of my face?