Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zagzebski
Creator
Creator

Date not working in where clause

In the script below I can't get the where clause in the "Retention Customer" table to work.

The variable vRetentionDateProduction = 1/1/2013.

If I hard code into my where clause:  Where ProdGLMonth <= makedate(2013,01,01) it works fine.  But for some reason it won't intepret the variable (vRetentionDateProduction) accurately. Am I doing something wrong?

Production_MaxDate:
LOAD
Max(Date([ProdGLMonth])) as vRetentionDateProduction
Resident Production;

LET vRetentionDateProduction = Peek('vRetentionDateProduction', 0, 'Production_MaxDate');

Retention_Customer:
Load
sum([Commission]) as [Commission Cube Pre]
Resident Production
Where ProdGLMonth <= $(vRetentionDateProduction);

Thanks, Steve

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try quoting your variable:

Where ProdGLMonth <= '$(vRetentionDateProduction)';


or use a number:


LET vRetentionDateProduction = num(Peek('vRetentionDateProduction', 0, 'Production_MaxDate'));

View solution in original post

4 Replies
swuehl
MVP
MVP

Try quoting your variable:

Where ProdGLMonth <= '$(vRetentionDateProduction)';


or use a number:


LET vRetentionDateProduction = num(Peek('vRetentionDateProduction', 0, 'Production_MaxDate'));

zagzebski
Creator
Creator
Author

Thanks, that was my problem!

Steve

zagzebski
Creator
Creator
Author

Figured I would try to hit you up on a related issue if thats ok ...

In the following statement...

 

year($(vRetentionDateProduction))& month($(vRetentionDateProduction))

All I get is 1899Dec for the above. Obviously I a doing something wrong. Since the variable above equals 10/1/2013 I was hoping to return the year month combintion of ....201310

swuehl
MVP
MVP

If your default date format is set correctly, quoting the variable should also work here (because QV then can interpret your string as date number, which is expected by the date functions year() and month():

year('$(vRetentionDateProduction)')& month('$(vRetentionDateProduction)')

If you used the second suggested approach using the num() function in the variable definition, it should work without quoting.