topic Re: Difference between two dates in QlikView

Difference between two dates

tmumaw — Wed, 14 Feb 2018 20:10:15 GMT

Hi all,

I'm trying to get the number of days between 2 dates with one date being today. I am using this in my script but getting script error. Any idea what might be wroing?

avg(Ceil([MaxDate] - '$(vToday)')) as NoDays

Re: Difference between two dates

vishsaggi — Wed, 14 Feb 2018 20:57:10 GMT

You are using avg() which is an aggregate function. If you use aggregate function in your load script you have to use Group By Clause. So like

LOAD *, avg(yourexpr) as NoDays

Resident Tablename

Group By FieldName;

Re: Difference between two dates

tmumaw — Thu, 15 Feb 2018 11:58:11 GMT

Thanks. That should work. I have another issue though and just noticed it. For some reason my variable for vToday is coming out as 1899-12-30 and it should be today's date. Here is my script: thanks for all your help.

IMRG:
Load
[Measuring point_POINT],
Max(Date([Billing Date],'YYYY-MM-DD')) as MaxDate,
Date($(vToday),'YYYY-MM-DD') as TodayDate,
Sum(NoHrs) as Hours
Resident IMRGTemp
Group by [Measuring point_POINT];

Re: Difference between two dates

johanlindell — Thu, 15 Feb 2018 12:12:44 GMT

Check the format of your vToday variable. If it is a date (2018-02-15) and not a number 43146 your formula needs to slightly changed.

If it is a date

TRACE $(vToday); returns 2018-02-15 then

Date($(vToday),'YYYY-MM-DD') as TodayDate,

Resolves to Date(2018-02-15,'YYYY-MM-DD') as TodayDate => Date(2001,'YYYY-MM-DD') as TodayDate,

Change to

Date('$(vToday)','YYYY-MM-DD') as TodayDate,

Date($(vToday),'YYYY-MM-DD') as TodayDate,

only works if TRACE $(vToday); returns 43146.

Re: Difference between two dates

tmumaw — Thu, 15 Feb 2018 12:19:09 GMT

Thanks. This one worked perfectly.

Date('$(vToday)','YYYY-MM-DD') as TodayDate