Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Compare Current Period with Previous Period Mistake

ziadm
Valued Contributor

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.

Version history
Revision #:
1 of 1
Last update:
‎02-23-2017 04:38 PM
Updated by: