Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
It is rather timeconsuming trying to spot your problem without some test data and some reference data (like a spreadsheet)...
Is it possible for you to provide test data, test appliction and reference data - that would be awesome - thanks...
will see what I can do