2 Replies Latest reply: Jul 27, 2018 7:04 AM by Del Booth RSS

    Missing data records

    Del Booth

      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