1 Reply Latest reply: Nov 6, 2013 5:41 PM by susant Kumar swain RSS

    Set Analysis - Custom Periods

    Jennie Elliott

      I need to make the master calendar follow a specific reporting period based on the following structure. This calendar will also inlcude normal reporting periods of MTD, YTD etc.

       

      This calendar need is specific to the data being pulled from the application. Basically I need to say something to the effect of if today's date = vToday then show data from Period 3 of Current Year. Also the same thing by Pay Period Week. So Period 1 of Year 2011 contains Pay Period Week of 1-4 while Period 2 of Year 2011 Contains Pay Period Week 5-8 and so on...

       

      I have a spreadsheet with all the dates and periods up until 2015.

       

      I have just been stuck on how to get the calendar to work right. If I can get this then the rest is easy.

       

      Calendar script is below as well. Not something I started, just trying to get it to work....

       

      PERIODPay Period DatePay Period WeekPay Period Year
      11/9/201112011
      11/10/201112011
      11/11/201112011
      11/12/201112011
      11/13/201112011
      11/14/201112011
      11/15/201112011
      11/16/201122011
      11/17/201122011
      11/18/201122011
      11/19/201122011
      11/20/201122011
      11/21/201122011
      11/22/201122011
      11/23/201132011
      11/24/201132011
      11/25/201132011
      11/26/201132011
      11/27/201132011
      11/28/201132011
      11/29/201132011
      11/30/201142011
      11/31/201142011
      12/1/201142011
      12/2/201142011
      12/3/201142011
      12/4/201142011
      12/5/201142011
      22/6/201152011
      22/7/201152011
      22/8/201152011
      22/9/201152011
      22/10/201152011
      22/11/201152011
      22/12/201152011
      22/13/201162011
      22/14/201162011
      22/15/201162011

       

       

      LET vMaxPayYearnum(Year(Today()));

      LET vMaxPayDatenum(Today());



      //StoreMaxPeriod

      StoreMaxPeriod:

      LOAD Distinct

       
      Max(PERIOD) as MaxPeriod

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1)

      Where Num([Pay Period Date]) = $(vMaxPayDate);



      LET vMaxPeriod = Num(Peek('MaxPeriod', 0, 'StoreMaxPeriod'));

      DROP Table StoreMaxPeriod;



      //StoreMaxPayWeek

      StoreMaxWeek:

      LOAD Distinct

       
      Max([Pay Period Week]) as MaxPayWeek

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1)

      Where Num([Pay Period Date]) = $(vMaxPayDate);



      LET vMaxPayWeek = Num(Peek('MaxPayWeek', 0, 'StoreMaxPayWeek'));

      DROP Table StoreMaxWeek;



      MapCurrentYearPayPeriod:

      Mapping LOAD Distinct

          
      Date([Pay Period Date]) as PayPeriodDate,

          
      If((PERIOD = $(vMaxPeriod)) and ([Pay Period Year] = $(vMaxPayYear)), 1, 0) as CurrentPeriod

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1);



      MapLastYearPayPeriod:

      Mapping LOAD Distinct

          
      Date([Pay Period Date]) as PayPeriodDate,

          
      If((PERIOD = $(vMaxPeriod)) and ([Pay Period Year] = $(vMaxPayYear)-1), 1, 0) as LastPeriod

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1);





      MapCurrentYearPayPeriodToDate:

      Mapping LOAD Distinct

          
      Date([Pay Period Date]) as PayPeriodDate,

          
      If((PERIOD = $(vMaxPeriod)) and ([Pay Period Week] <= $(vMaxPayWeek)) and ([Pay Period Year] = $(vMaxPayYear)), 1, 0) as PeriodToDate

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1);



      //****************************************************************************************************************************************

      Mapping LOAD Distinct

          
      Date([Pay Period Date]) as PayPeriodDate,

          
      If(PERIOD=$(vMaxPeriod), IF([Pay Period Week] = $(vMaxPayWeek), IF([Pay Period Year] = $(vMaxPayYear),1,0))) as CurrentPeriodWeek

          
      //If((PERIOD = $(vMaxPeriod)) and ([Pay Period Week] = $(vMaxPayWeek)) and ([Pay Period Year] = $(vMaxPayYear)), 1, 0) as CurrentPeriodWeek

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1);



      //****************************************************************************************************************************************



      MapLastYearPayPeriod:

      Mapping LOAD Distinct

          
      Date([Pay Period Date]) as LYPayPeriodDate,

          
      If((PERIOD = $(vMaxPeriod)) and ([Pay Period Week] <= $(vMaxPayWeek)) and ([Pay Period Year] = $(vMaxPayYear)-1), 1, 0) as LastYearPeriodToDate

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1);



      MapSecondYearPriorPayPeriod:

      Mapping LOAD Distinct

          
      Date([Pay Period Date]) as SYPayPeriodDate,

          
      If((PERIOD = $(vMaxPeriod)) and ([Pay Period Week] <= $(vMaxPayWeek)) and ([Pay Period Year] = $(vMaxPayYear)-2), 1, 0) as SecondLastYearPeriodToDate

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1);



      MapLastPayPeriod:

      Mapping LOAD Distinct

          
      Date([Pay Period Date]) as LYPayPeriodDate,

          
      If((PERIOD = $(vMaxPeriod)-1) and ([Pay Period Year] = $(vMaxPayYear)), 1, 0) as LastPeriodToDate

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1);



      MapSecondPriorPayPeriod:

      Mapping LOAD Distinct

          
      Date([Pay Period Date]) as SYPayPeriodDate,

          
      If((PERIOD = $(vMaxPeriod)-2) and ([Pay Period Year] = $(vMaxPayYear)), 1, 0) as SecondLastPeriodToDate

      FROM

      [..\..\..\QlikView Production\Files\Pay Periods.xlsx]

      (
      ooxml, embedded labels, table is Sheet1);



      //CurrentPeriodMap:

      //Mapping LOAD Distinct

      // Period,

      //     PayPeriodDate,

      //     If(PayPeriodDate = $(vToday),1,0) as CAL|CurrentPeriod,

      //     PayPeriodWeek,

      //     PayPeriodYear,

      //     PeriodRef,

      //     PP|Ref

      //FROM

      //[C:\QlikView\QlikView Development\QVDocuments\SourceDocuments\QVD\TS\PayPeriods.qvd]

      //(qvd);



      /*************** MinMax Table *************



      Keeps minimum and maximum Date value from Facts table



      */




      MinMax:

      LOAD

      Min(Date) as MinDate,

      Max(Date) as MaxDate

      RESIDENT Invoice;



      LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

      LET vToday = $(vMaxDate);



      /*************** Temporary Calendar *************



      Generates a single table with one field containing

      all existing dates between MinDate and MaxDate.



      */




      TempCal:

      LOAD

      date($(vMinDate) + rowno() - 1) as TempDate

      AUTOGENERATE

      $(vMaxDate) - $(vMinDate) + 1;



      DROP TABLE MinMax;



      //*************** Master Calendar ***************



      MasterCalendar:

      LOAD

      TempDate AS Date,

      Week(TempDate) AS Week,

      Year(TempDate) AS Year,

      Month(TempDate) AS Month,

      Day(TempDate) AS Day,

      Weekday(TempDate) AS WeekDay,

      'Q' &
      ceil(month(TempDate) / 3) AS Quarter,

      Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

      Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

      IF(WeekDay(TempDate)='Sun',0, Applymap('HolidayMap',Num(TempDate), 1)) as WorkDayCounter,



      //Year Flags

      inyeartodate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,

      inyeartodate(TempDate, $(vToday), -1) * -1 as LastYTDFlag ,



      // Week Flags

      inweek(TempDate, $(vToday), 0) * -1 as CurrentWeek, //Current week

      inweek(TempDate, $(vToday), -1) * -1 as LastWeek, //Last week



      // Week-to-Date Flags

      inweektodate(TempDate, $(vToday), 0) * -1 as CurrentWeekTD, //Current week-to-date



      // Year flags

      inyear(TempDate, $(vToday), 0) * -1 AS CY, //Current year

      inyear(TempDate, $(vToday), -1) * -1 AS FPY, //First prior year

      inyear(TempDate, $(vToday), -2) * -1 AS SPY, //Second prior year

      inyear(TempDate, $(vToday), 1) * -1 AS NY,     //Next year

      inyear(TempDate, $(vToday), -1) * -1 AS LY,     //Last year (Same as FPY)



      // Year-to-date flags

      inyeartodate(TempDate, $(vToday), 0) * -1 AS CYTD, //Current year-to-date

      inyeartodate(TempDate, $(vToday), -1) * -1 AS FPYTD, //First prior year-to-date

      inyeartodate(TempDate, $(vToday), -2) * -1 AS SPYTD, //Second prior year-to-date

      inyeartodate(TempDate, $(vToday), 1) * -1 AS NYTD, //Next year



      //Year to Date Flags

      If(InYearToDate(TempDate, $(vToday), 0)

               
      or InYearToDate(TempDate, $(vToday), -1)

               
      or InYearToDate(TempDate, $(vToday), -2)

               
      or InYearToDate(TempDate, $(vToday), -3)

               
      or InYearToDate(TempDate, $(vToday), -4)

                     , 'YTD')
      as YTD,

                    

      //  Pay Period Flags

      ApplyMap('MapCurrentYearPayPeriodToDate',date(TempDate),'No Pay Period') as PeriodToDate,

      ApplyMap('MapLastYearPayPeriod',date(TempDate),'No Pay Period') as LastYearPeriodToDate,

      ApplyMap('MapSecondYearPriorPayPeriod',date(TempDate),'No Pay Period') as SecondLastYearPeriodToDate,

      ApplyMap('MapLastPayPeriod',date(TempDate),'No Pay Period') as LastPeriodToDate,

      ApplyMap('MapSecondPriorPayPeriod',date(TempDate),'No Pay Period') as SecondLastPeriodToDate,

      ApplyMap('MapCurrentYearPayPeriod',date(TempDate),'No Pay Period') as CurrentPeriod,

      ApplyMap('MapCurrentYearPayPeriodWeek',date(TempDate),'No Pay Period') as CurrentPeriodWeek,

      ApplyMap('MapLastYearPayPeriod',date(TempDate),'No Pay Period') as LastYearPayPeriod



      RESIDENT TempCal

      ORDER BY TempDate ASC;



      DROP TABLE TempCal;

       

        • Re: Set Analysis - Custom Periods
          susant Kumar swain

          Hi,

          Can you elaborate what you want ?

          As you mentioned you have a excel of custom period then do the left join with your qlikview calender (Last step of your code) give all custom period correspond to normal period.

           

          I think only to create custom YTD you need to use YearStart with offset (if required).


          Sorry if i repeat something you know or you have tried before.