Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.