topic Create calendar table that defines pay schedule in QlikView

Create calendar table that defines pay schedule

Fri, 17 Feb 2012 16:05:25 GMT

Is there a way to create a calendar table that defines the pay schedule?

We get paid every 2 weeks, but the specific pay date is not recorded in our system.

So say today is the day everyone gets their check. Today's Check (02/17/2012) includes the dates 01/30/2012 - 02/12/2012

I want to create a master calendar that I can tie into ours time ticket table that will allow me to group the correct dates into each pay schedule.

Is something like this possible?

Create calendar table that defines pay schedule

Anonymous — Fri, 17 Feb 2012 18:00:43 GMT

Here as a possible solution.

First, take a known payday, and store it in a variable, for example:

LET D = makedate(2012,2,17);

Next, in your master calendar, add a flag, so PayDay=1 means it is a payday

if(mod(Date-'$(D)',14)=0,1,0) as PayDay

Create calendar table that defines pay schedule

Fri, 17 Feb 2012 19:04:07 GMT

This would be close, but would it be possible to list the date for the scheduled pay with each date?

Say instead of having a flag just for the 17th, the dates from 01/30/2012-02/12/2012 would have a corresponding field that has the value 02/17/2012?

Create calendar table that defines pay schedule

Anonymous — Fri, 17 Feb 2012 19:07:49 GMT

Sure, it is the same:

if(mod(Date-'$(D)',14)=0,Date) as PayDay

Create calendar table that defines pay schedule

Mon, 20 Feb 2012 12:05:58 GMT

I tried this expression and it does not return anything, just - for missing or null

Create calendar table that defines pay schedule

Anonymous — Mon, 20 Feb 2012 12:18:57 GMT

What could be wrong:
- Check your variable - what it shows if you put it in a text box?
- Make sure your Date field is the integer date, not a timestamp. If you're not sure, add floor() function:
if(mod(floor(Date)-'$(D)',14)=0,Date)

Re: Create calendar table that defines pay schedule

Mon, 20 Feb 2012 12:51:08 GMT

When the variable is placed in a text box it returns this: 6.2189054726368e-005 which when the Date function is applied returns this: 12/30/1899

Edit:

Nevermind I forgot to change the variable to the one I used in the expression.

Still this is doing the same thing as the flag. On the actual pay day it displays the date. So say for the date 02/17/2012 it displays 02/17/2012.

I need it to display 02/17/2012 for all the dates between 01/30/2012 and 02/12/2012. 02/17/2012 should display the pay date 03/02/2012.

Re: Create calendar table that defines pay schedule

CELAMBARASAN — Mon, 20 Feb 2012 13:01:35 GMT

Hi,

How you stored the 01/30/2012 - 02/12/2012 in table?

I mean whole thing in single field or stored as from and to?

Celambarasan

Re: Create calendar table that defines pay schedule

Mon, 20 Feb 2012 13:02:57 GMT

It is a single field that is recorded for the time ticket date. There is no reference on the record for when the actual pay date is.

Re: Create calendar table that defines pay schedule

CELAMBARASAN — Mon, 20 Feb 2012 13:30:50 GMT

Hi,

Can you attach a sample data?

Celambarasan

Re: Create calendar table that defines pay schedule

Mon, 20 Feb 2012 14:16:10 GMT

Here is the calendar code I am using:

Calendar:  
LET vDateMin = Num(MakeDate(2010,1,1));  
LET vDateMax = Floor(MonthEnd(Today()));  
LET vDateToday = Num(Today());  
LET PD = Date(makedate(2010,1,08));
TempCalendar:  
Left Keep (PayCore)
LOAD 
$(vDateMin) + RowNo() - 1 AS DateNumber,  
Date($(vDateMin) + RowNo() - 1) AS TempDate  
AUTOGENERATE 1  
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);  
Calendar:  
LOAD 
Date(TempDate) AS CalendarDate,  
Date(TempDate) AS  calendarlink,
if(mod(floor(TempDate)-'$(PD)',14)=0,TempDate) as PayDay,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,  
WeekDay(TempDate) AS CalendarDayName,  
Week(TempDate) AS CalendarWeekOfYear,  
Month(TempDate) AS CalendarMonthName,  
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,  
Year(TempDate) AS CalendarYear,  
// Calendar Date Names  
WeekName(TempDate) as CalendarWeekNumberAndYear,  
MonthName(TempDate) as CalendarMonthAndYear,  
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,  
// Start Dates  
DayStart(TempDate) as CalendarDayStart,  
WeekStart(TempDate) as CalendarWeekStart,  
MonthStart(TempDate) as CalendarMonthStart,  
QuarterStart(TempDate) as CalendarQuarterStart,  
YearStart(TempDate) as CalendarYearStart,  
// End Dates  
DayEnd(TempDate) as CalendarDayEnd,  
WeekEnd(TempDate) as CalendarWeekEnd,  
MonthEnd(TempDate) as CalendarMonthEnd,  
QuarterEnd(TempDate) as CalendarQuarterEnd,  
YearEnd(TempDate) as CalendarYearEnd,  
// Combo Date Examples  
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,  
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,  
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays  
RESIDENT TempCalendar ORDER BY TempDate ASC;  
DROP TABLE TempCalendar;  
LET vDateMin = Num(MakeDate(2010,1,1));  
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));  
LET vDateToday = Num(Today());

Create calendar table that defines pay schedule

Anonymous — Mon, 20 Feb 2012 23:09:57 GMT

I see, it's not what I thought... You want a value of payday for each date, and the rules are according to your example. If I got the requirement right this time, here it is:

=if(mod(date(floor(weekend(Date))) -2- $(PD), 14)=0,
date(floor(weekend(Date)+12)),
date(floor(weekend(Date)+5)))

I tried it in a calculated list box. Can work as in expression in the chart or in the script, as an additional field in the master calendar.

Regards,

Michael