topic Re: Date Comparisons in QlikView

Date Comparisons

Sun, 25 May 2014 01:55:50 GMT

I have the code below where I'm comparing 2 dates to exit the loop. The loop does not exit, even if the vTmpDate becomes > vMaxDate. What is the correct way of performing date comparisons? Thanks in advance!

~ ~ ~ ~ ~

let vMinDate = MakeDate(2013,06,30);

let vMaxDate = MakeDate(2014,04,30);

let vTmpDate = vMinDate;

let i = 0;

DO WHILE Date#('$(vTmpDate)') <= Date#('$(vMaxDate)')


	let i = i + 1;
	let vTmpDate = MonthEnd('$(vMinDate)',i);

	TRACE '$(vTmpDate)';

LOOP

Re: Date Comparisons

Sun, 25 May 2014 10:56:49 GMT

I was able to fix the issue by using the actual variables instead of doing a Dollar Sign Expansion $(...) in the loop condition:

DO WHILE vTmpDate <= vMaxDate

But now I'm confused as when to use DSE versus when to use the actual variables. I've read that it is a best practice to always use DSE. Can someone shed some light on this?

Re: Date Comparisons

rwunderlich — Sun, 25 May 2014 15:13:57 GMT

You have found the right solution. Your problem was due to the specifics of the DO statement. From the Help doc:

"Each condition is interpreted only the first time it is encountered but is evaluated for every time it encountered in the loop"

DSE is interpretation, and interpretation happens only once in DO. At start of the DO, "Date#('$(vTmpDate)')" was getting interpreted to

"Date#('6/30/2013')".

On each interation of the loop, that was getting evaluated, but $(vTmpDate) was not getting interpreted again. Therefore the evaluation was always using "Date#('6/30/2013')".

Here's a good post by HIC on the subject of DSE.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/18/dollar-expansions

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Date Comparisons

Sun, 25 May 2014 20:23:40 GMT

Thanks Rob Wunderlich for the clear explanation and supporting links. Very much appreciated.