Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
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];
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.
Thanks. This one worked perfectly.
Date('$(vToday)','YYYY-MM-DD') as TodayDate