28 Replies Latest reply: Sep 1, 2017 10:25 AM by Sunny Talwar RSS

    CALENDAR MEASURE YTD LAST YEAR QLIK SENSE

    Philippe BONNIN

      Hi

      To explain my issue i created a simple sample ;

       

      I have 2 files with common dates (3 years ) as shown

      fichiers.png

      Then I created  a calendar measure on CATTC field

       

      measures.png

      So now, i want to  create a graph with comparative analysis CATTC historical / real / goal  over th entire periode (obviously 2 years about the historical)

       

      I obtain that

      graph.png

      my issue :

      CATTC real = CATTC last Year on 2016

      No CATTC historical on 2017

       

      as you see, i don't understand the basic concept :-(

       

      I need some help

       

      regards

       

      Philippe

       

        • Re: CALENDAR MEASURE YTD LAST YEAR QLIK SENSE
          Sunny Talwar

          I am not sure I understand your issue... would you be able to elaborate?

          • Re: CALENDAR MEASURE YTD LAST YEAR QLIK SENSE
            Sunny Talwar

            Something along these lines

             

            Table:

            LOAD Date(MakeDate(ANNEE, MOIS), 'M_YYYY') as MonthYear,

            ANNEE,

                MOIS,

                CATTC

            FROM [lib://Lib/HIS.xlsx]

            (ooxml, embedded labels, table is REEL);

             

            Goal:

            LOAD [CATTC GOAL],

                 Date(MakeDate(ANNEE, MOIS), 'M_YYYY') as MonthYear

            FROM [lib://CALENDAR/PREV.xlsx]

            (ooxml, embedded labels, table is PREVU);

             

            AsOfTable:

            LOAD MonthYear as AsOfMonthYear,

            MonthYear,

                 'CY' as Flag

            Resident Table;

             

            Concatenate (AsOfTable)

            LOAD MonthYear as AsOfMonthYear,

            Date(AddYears(MonthYear, -1), 'M_YYYY') as MonthYear,

                 'PY' as Flag

            Resident Table;

             

            Concatenate (AsOfTable)

            LOAD MonthYear as AsOfMonthYear,

            Date(AddYears(MonthYear, -1), 'M_YYYY') as MonthYear,

                 'Goal' as Flag

            Resident Goal;


            QuartersMap: 

            MAPPING LOAD  

            rowno() as Month, 

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

            AUTOGENERATE (12); 

             

            Temp: 

            Load 

                           min(AsOfMonthYear) as minDate, 

                           max(AsOfMonthYear) as maxDate 

            Resident AsOfTable

             

            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); 

             

            MasterCalendar: 

            Load 

                           TempDate AS AsOfMonthYear

                           week(TempDate) As Week, 

                           Year(TempDate) As Year, 

                           Month(TempDate) As Month, 

                           Day(TempDate) As Day, 

                           YeartoDate(TempDate)*-1 as CurYTDFlag, 

                           YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                           inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                           date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                           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;