11 Replies Latest reply: Sep 14, 2015 3:29 AM by Serhan Celebi RSS

    problem with master calendar and filtered data

    Dirk Weber

      Hi altogether,

       

      I am using Qlik Sense Desktop 1.1.0 and have the following problem.

       

      I want to show some data about orders. All data from a certain year is shown in a separate line chart. Important is, that also for the current year all twelve months should be shown, even if there is no order yet, i.e. June till December 2015 should be shown as zero values.

       

      I created a master calendar and connected it to the order table by the same field name ‘OrderDate’.

       

      When I start the app the first time everything looks fine (see Screen 1). In the first line Chart for the current year, the months June to Dec are shown with 0 values. But as soon as I click on the filter panel and select a product number, only the months are shown where the product was sold, but no longer the months June to Dec. (see screen 2).

       

      You can also see in the table on the right side that shows all OrderDates, that without filtering there are no gaps in the dates. As soon the article is filtered there are gaps, i.e. some days are missing where the article was not sold.

       

      Anybody has an idea what is going wrong?

      Thanks

       

      -------------------

       

      The data for the line chart for the current year is :

      Dimension Field:

      MonthYear

       

      Measure Field:

      Sum({$<Year={2015}>} nNumber)

       

       

      Script of order table:

      LOAD kBestellung,

          tRechnung_kRechnung,

          tBenutzer_kBenutzer,

          tAdresse_kAdresse,

          tText_kText,

          tKunde_kKunde,

          cBestellNr,

          cType,

          date(floor(dErstellt)) as OrderDate, //cut off time and convert to date format again

       

         …

       

          MMYY,

          YYYY,

          MM;

      SQL SELECT kBestellung,

          tRechnung_kRechnung,

          tBenutzer_kBenutzer,

          tAdresse_kAdresse,

          tText_kText,

          tKunde_kKunde,

          cBestellNr,

          cType,

          dErstellt,

       

         …

       

      Right('00'+Convert(varchar, DATEPART(month, [dErstellt])), 2)+'/'+cast(DATEPART(yy, [dErstellt]) as varchar(4)) as MMYY,

          Year(dErstellt) as YYYY,

      Right('00'+Convert(varchar, DATEPART(month, [dErstellt])), 2) as MM

      FROM torder;

       

       

      Master calendar script:

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month,

      'Q' & Ceil (rowno()/3) as Quarter 

      AUTOGENERATE (12);

           

      LET varMinDate = Floor(MakeDate(year(AddYears(Today(),-2)), 1 ,1)); //Heute vor 2 Jahren 1.1.13 /floor statt num

      LET varMaxDate = Floor(MakeDate(year(Today()),12,31)); //Heute bis Ende dieses Jahres

           

      TempCalendar: 

      LOAD 

      $(varMinDate) + Iterno()-1 As Num, 

      Date($(varMinDate) + IterNo() - 1) as TempDate 

      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

           

      MasterCalendar:

      Load 

        TempDate AS OrderDate, 

      WeekYear(TempDate) &'/ S'& week(TempDate) AS id_Date,

        week(TempDate) As Week, 

        Year(TempDate) As Year, 

      Month(TempDate) &'/'& Year(TempDate) AS MonthYear,

      Month(TempDate) As Month,

        Day(TempDate) As Day, 

      ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

      Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

      WeekDay(TempDate) as WeekDay 

      Resident TempCalendar

      Order By TempDate ASC;

      Drop Table TempCalendar;