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?
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.
