Compare Current Period with Previous Period Mistake

    A common metric with a business is to compare  current period with previous period a over same number of days.

    few fall into mistake of comparison of current period with the whole previous period.  Take for example Month To Date Sales compared with Previous Month.   If the current date is 15 of the month the comparison is made over 15 days of the current month with 30 days of the previous month and this is not apple to apple comparison and the result will be on always down until reach a day of 30.  To overcome this I have created a master calendar to overcome the period comparison for WTD,MTD,QTD and YTD flags along with previous period Flags.

     

       if (TempDate >= monthstart($(vToday),-1) and TempDate <= monthstart($(vToday),-1)+ $(vToday)-monthstart($(vToday)) ,1,0) as PrMTDFlag,  //Prior Month Flag

     

    in this Prior Month Flag is set over only same number of days for the current period.  Same follows for MTD vs PrMTD, QTD vs PrQTD and YTD vs PrYTD

     

     

    The Script for creating an apple to apple Period Flags :

     

     

    MinMax:

    LOAD

      Min([Sales Date]) as MinDate,

      Max([Sales Date]) as MaxDate

    RESIDENT [Sales];

     

     

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

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

    LET vToday = $(vMaxDate);

    //LET vToday = Num(Today(0));

     

     

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

     

     

    DROP TABLE MinMax;

     

     

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

     

     

    Disconnected during the Date Island exercise by renaming TempDate as IslandDate

     

     

    */

     

     

     

     

    SalesCalendar:

     

     

    LOAD

     

     

    // TempDate AS CalDate,

        TempDate AS [Sales Date],

      'Week-' & Week(TempDate) AS WeekDesc,

      Year(TempDate) AS Year,

      Month(TempDate) AS Month,

      Day(TempDate) AS Day,

      Weekday(TempDate) AS WeekDay,

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

      makedate(year(TempDate),month(TempDate)) as MonthStartdate,

      date(rangemin(dayname(monthend(TempDate)),today())) as MonthEnddate , //Creating the field MonthEndDate

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

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

     

        InWeekToDate(TempDate, $(vToday), 0) * -1 AS CurWTDFlag,

        InWeekToDate(TempDate, $(vToday), -1) * -1 AS PrWTDFlag,

        InMonthToDate(TempDate, $(vToday), 0) * -1 AS CurMTDFlag,

        if (TempDate >= monthstart($(vToday),-1) and TempDate <= monthstart($(vToday),-1)+ $(vToday)-monthstart($(vToday)) ,1,0) as PrMTDFlag,  //Prior Month Flag

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

        if (TempDate >= YearStart($(vToday),-1) and TempDate <= YearStart($(vToday),-1)+ $(vToday)-YearStart($(vToday)) ,1,0) as PrYTDFlag,  //Prior Year Flag

        InQuarterToDate(TempDate, $(vToday), 0) * -1 AS CurQTDFlag,                                                                   

        if (TempDate >= QuarterStart($(vToday),-1) and TempDate <= Quarterstart($(vToday),-1)+ $(vToday)-QuarterStart($(vToday)) ,1,0) as PrQTDFlag  // Prior Quarter Flag

     

     

     

    Then YTDSales =

    sum({$<CurYTDFlag ={1},Year=,Quarter=,Month=,WeekDesc= >}  [Sales Amount])

    PrYTD Sales

    sum({$<CurPrYTDFlag ={1},Year=,Quarter=,Month=,WeekDesc= >}  [Sales Amount])

     

    and YTDCompassion =

    num(  ( $(YTDSales ) - $(PrYTD Sales ))  /  $(PrYTD Sales ) , '#,##0%')

     

    This way is more efficient than having a complex set analysis by adding these line to the Master Calendar and setting flags.