Convert Datediff from SQL to Qlikview

[kalyandg]

Hi,

I am having a requirement, like convert SQL queries into Qlikview format and store it as QVD.

One of the where condition, like date difference query should be converted to Qlikview style.

datediff( day, InvDate , @StartDate ) <=0 and datediff( day, InvDate , @EndDate ) >=0

I have tried using

1. (date(InvOrderDate,'DD/MM/YYYY')>=$(vStartDate) and date(InvOrderDate,'DD/MM/YYYY')<=$(vEndDate))

2. ($(vStartDate)-date(InvOrderDate,'DD/MM/YYYY')<=0 and $(vEndDate)-date(InvOrderDate,'DD/MM/YYYY')>=0 )

But its not working.

please help.

Thanks,

Kalyan

[zhadrakas]

please try like this.

Interval(@EndDate-@StartDate,'d')

[kalyandg]

Hi Tim Driller,

I have tried like this

(Interval($(vStartDate)-date(InvOrderDate,'DD/MM/YYYY'))<=0 and Interval($(vEndDate)-date(InvOrderDate,'DD/MM/YYYY'))>=0)

but its not working

[zhadrakas]

1) note that you Need to write , 'd' to calculate the interval in days

2) the first date (in this case @EndDate) should always be the higher date

Interval(@EndDate-@StartDate,  'd')

[kalyandg]

Hi Tim,

I have tried like this

(Interval($(vStartDate)-date(InvOrderDate,'DD/MM/YYYY'),'d')<=0 and Interval($(vEndDate)-date(InvOrderDate,'DD/MM/YYYY'),'d')>=0)

actually, we are extracting data according to the Start and End date, with the "InvOrderDate" column.

User will enter the start date and end date in front end, according to that, data should extract.

Thanks,

Kalyan

[zhadrakas]

i think you Need to a it like this

(Interval(date(InvOrderDate,'DD/MM/YYYY')- $(vStartDate),'d')>=0 and Interval($(vEndDate)-date(InvOrderDate,'DD/MM/YYYY'),'d')>=0)

i would test it by creating text boxes with that interval expressions to see if it works as suspected

Interval(date(InvOrderDate,'DD/MM/YYYY')- $(vStartDate),'d')