Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

delbooth
New Contributor II

Missing data records

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

2 Replies
MVP
MVP

Re: Missing data records

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

delbooth
New Contributor II

Re: Missing data records

will see what I can do

Community Browser