topic Re: date difference formula in qliksense load script in App Development

date difference formula in qliksense load script

daveatkins — Mon, 14 Nov 2022 16:04:28 GMT

Can someone explain the correct syntax for this simple edit to a load script? My immediate problem is with the attempt to calculate the date diff to set a flag for Last7d.

"Date" is a field in the data which is also a date type.

Let vStartDate = date('5/1/2022');
Let vEndDate = date(WeekEnd(Today()-7)); //last Saturday

LOAD *
//,if(Today()-Date <=7 and Today()-Date>=1, 1) as Last7d //works
,if($(vEndDate) - Date <=7 and $(vEndDate) - Date >= 1, 1) as Last7d //generalizing

,$(vEndDate) - Date as DaysDiff //debugging
,if(Today()-Date <=28 and Today()-Date>=1, 1) as Last4w
,if(MonthStart(Date) = MonthStart(AddMonths(Today(),-1)),MonthName(Date)) as PreviousMonth
,if(Today()-Date <=180 and Today()-Date >= 1, 1) as Last6Months
,ApplyMap('RegionMap', Corp,'No Region Mapped') As Region
Resident FactTable
WHERE Date <= Today()-1
and Date >= '$(vStartDate)';

I understand why this fails; it interprets the variable by dividing day, year, etc. so the result is 0.0something - a date resulting in -44875 as the value of the operation.

If I take away the $() then no field found.

There is seriously no logical rationale to how Qlik handles dates. I have still not figured it out after 9 years. Always trial and error. The where clause above works but is logically inconsistent. $() should be returning a date...why are single quotes needed?

Re: date difference formula in qliksense load script

BrunPierre — Mon, 14 Nov 2022 19:04:31 GMT

Interval('$(vEndDate)' - Date,'dd') as DaysDiff

Re: date difference formula in qliksense load script

daveatkins — Mon, 14 Nov 2022 20:48:24 GMT

The solution appears to be the generous application of single quotes:

,if('$(vEndDate)' - Date <=7 and '$(vEndDate)' - Date >= 1, 1) as Last7d
,if('$(vEndDate)' - Date <=28 and '$(vEndDate)' - Date >= 1, 1) as Last4w
,if(MonthStart(Date) = MonthStart(AddMonths('$(vEndDate)',-1)),MonthName(Date)) as PreviousMonth
,if('$(vEndDate)'-Date <=180 and '$(vEndDate)'-Date >= 1, 1) as Last6Months

I did not try interval because the documentation says that is a formatting function.