Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try quoting your variable:
Where ProdGLMonth <= '$(vRetentionDateProduction)';
or use a number:
LET vRetentionDateProduction = num(Peek('vRetentionDateProduction', 0, 'Production_MaxDate'));
Try quoting your variable:
Where ProdGLMonth <= '$(vRetentionDateProduction)';
or use a number:
LET vRetentionDateProduction = num(Peek('vRetentionDateProduction', 0, 'Production_MaxDate'));
Thanks, that was my problem!
Steve
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
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.