Hi.
I am using the below to get a value of material collected
Sum({<EntYear={"$(=year(Today()))"},[Material Type]={"lead"}, EntWeek ={"$(=week(Today())-1)"}>}[Quantity Collected] )
I have set a calendar up as below:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(Date_Entered) as minDate,
max(Date_Entered) as maxDate
Resident [All Orders];
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
EnterCalendar:
Load
TempDate AS Date_Entered,
week(TempDate) As EntWeek,
Year(TempDate) As EntYear,
Month(TempDate) As EntMonth,
Day(TempDate) As EntDay,
YeartoDate(TempDate)*-1 as EntCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as EntLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as EntRC12,
date(monthstart(TempDate), 'MMM-YYYY') as EntMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as EntQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as EntWeekYear,
WeekDay(TempDate) as EntWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
The set analysis expression gives the wrong total when comparing it to the excel data worksheet it is drawing it from, as I want to evaluate lead, collected in 2018 for the previous week ie week number 29.
is the issue my calendar, my expression or can qlik sense sometimes miss date records.
Thanks
Del
