Set Analysis - Custom Periods

jenmclean — Wed, 06 Nov 2013 19:47:47 GMT

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....

PERIOD	Pay Period Date	Pay Period Week	Pay Period Year
1	1/9/2011	1	2011
1	1/10/2011	1	2011
1	1/11/2011	1	2011
1	1/12/2011	1	2011
1	1/13/2011	1	2011
1	1/14/2011	1	2011
1	1/15/2011	1	2011
1	1/16/2011	2	2011
1	1/17/2011	2	2011
1	1/18/2011	2	2011
1	1/19/2011	2	2011
1	1/20/2011	2	2011
1	1/21/2011	2	2011
1	1/22/2011	2	2011
1	1/23/2011	3	2011
1	1/24/2011	3	2011
1	1/25/2011	3	2011
1	1/26/2011	3	2011
1	1/27/2011	3	2011
1	1/28/2011	3	2011
1	1/29/2011	3	2011
1	1/30/2011	4	2011
1	1/31/2011	4	2011
1	2/1/2011	4	2011
1	2/2/2011	4	2011
1	2/3/2011	4	2011
1	2/4/2011	4	2011
1	2/5/2011	4	2011
2	2/6/2011	5	2011
2	2/7/2011	5	2011
2	2/8/2011	5	2011
2	2/9/2011	5	2011
2	2/10/2011	5	2011
2	2/11/2011	5	2011
2	2/12/2011	5	2011
2	2/13/2011	6	2011
2	2/14/2011	6	2011
2	2/15/2011	6	2011

LET vMaxPayYear = num(Year(Today()));

LET vMaxPayDate = num(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

//

//(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

qliksus — Wed, 06 Nov 2013 22:41:46 GMT

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.

topic Set Analysis - Custom Periods in QlikView

Set Analysis - Custom Periods

Re: Set Analysis - Custom Periods