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: 
Not applicable

Transform Date to Number while keeping the value

Hey community

I am having the following issue:

I would like to reload data from a table within SAP. Since I only need the latest 3 months I applied this formula:

date(AddMonths(MonthStart(today()),-2),'YYYYMMDD') which would result by today in 20140801.

The problem is, if I put that into the where statement, it wouldn't work. If i put the plain numbers - hard coded - into it, it works. I think the solution would be, to transform the date format shown above, into a number. Unfortunatley, if I do that, it turns to 41852.

How would you guys go about such an issue?

regards

Sebastian

1 Solution

Accepted Solutions
rubenmarin

Following anbu's solution, if vDt has the value you want, maybe it work using () in where clause:

Let vDt= date(AddMonths(MonthStart(today()),-2),'YYYYMMDD');

Sql Select * from Table where DateField > '$(vDt)';

View solution in original post

8 Replies
alexandros17
Partner - Champion III
Partner - Champion III

All dates have a corresponding numerical value as you experienced, if you need the date again simply do

Date(41852
...
let me know
rubenmarin

Hi Sebastian, if the hard-coded number works you can try with the $-expansion formula:

$(=date(AddMonths(MonthStart(today()),-2),'YYYYMMDD'))

anbu1984
Master III
Master III

Let vDt= date(AddMonths(MonthStart(today()),-2),'YYYYMMDD');

Sql Select * from Table where DateField > '$vDt';

Not applicable
Author

Hey all,

I tried both, Ruben's and Anbu's proposal, unfortunatley it didn't work. Especially Anbu's solution seemed promising, since the LET statement should display the content of a variable as it is?

anbu1984
Master III
Master III

Can you post sample qvw

MarcoWedel

Text(date(AddMonths(MonthStart(today()),-2),'YYYYMMDD'))

or

Num#(Text(date(AddMonths(MonthStart(today()),-2),'YYYYMMDD')))

?

rubenmarin

Following anbu's solution, if vDt has the value you want, maybe it work using () in where clause:

Let vDt= date(AddMonths(MonthStart(today()),-2),'YYYYMMDD');

Sql Select * from Table where DateField > '$(vDt)';

Not applicable
Author

This one worked. The brackets were missing. I think though, that Marco's solution might work too. Going to try that...

Thanks guys!!