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?
