Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jenmclean
Contributor III
Contributor III

Set Analysis - Custom Periods

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

//

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

1 Reply
qliksus
Specialist II
Specialist II

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.