Qlik Community

QlikView Documents

Documents for QlikView related information.

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.

Comments
lukasmat
New Contributor

Hey Ziadm,

 

Thank you posting. I have similar calculations made on Qlikview but due to unknowm reason I cannot transfer it directly to Qlik sense. 

I've tried your code and it doesn't work either. It just sums all the sales or selected year sales but not YTD. Do you have any ideas what might be wrong? 

YTD:

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

Do I need to change names of "Year=,Quarter=,Month=,WeekDesc" to the field names used in table or do some other adjustments?

Appreciate your help. 

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