topic Re: Calendar with Pay Periods in QlikView

Calendar with Pay Periods

jenmclean — Thu, 08 Jan 2015 17:03:20 GMT

Attached is a calendar script that is working for my needs except for one issue; I need 13 pay periods and Pay Periods 1-12 generate the correct dates, but Pay Period 13 always goes from one year to the next so instead of PP13 giving me dates of 12/7/14 to 1/3/15, it shows me the entire year.

A lot of the data that I have to display is based on Pay Periods for compensation.

Any suggestions would be very much appreciated.

Thanks!

Re: Calendar with Pay Periods

Gabriel — Fri, 09 Jan 2015 15:12:26 GMT

Looking at the attached APP.

I think what can resolve this issue is to create another Flag (%PIT_30DaysForward or something like that) in the script under your calendar table that calculate 30 days from the start date of whatever PP you selected (i.e If I select PP9 pick the start date may be from WeekStart field or somewhere, you know your application and data well. Then determine 30 days or number of working days from that start date).

Then you can use new Flag in SET ANALYSIS to restrict selected PP, this way even if the Month cut across Years it doesn't matter because all it does is calculate 30 days from start date.

Hope this make sense.

Best Regards,

Gabriel

Re: Calendar with Pay Periods

jenmclean — Fri, 09 Jan 2015 16:45:56 GMT

Do you have an example of how that would be written?

Re: Calendar with Pay Periods

Colin-Albert — Fri, 09 Jan 2015 17:08:36 GMT

Try adding the script below to your Calendar script - adding another two preceeding loads to the top of the calendar script.

This will give you a FiscalYear & Period combined as 2015PP1, 2014PP13 , 2014PP12 etc

FiscalCalendar:

// Additions to get FiscalYear & Period sequence
Load *,
autonumber(FiscalYrPeriod, 'FiscalYrPeriod') as FiscalYrPeriodSeq; //Sequence No for FiscalYr & Period
Load *,
FiscalYear & Period AS FiscalYrPeriod; //FiscalYr & Period as YYYYPPNN

Then get the FiscalYrPeriodSeq for today's date by peeking out the value from a temp load after you have generated the calendar, using peek.

The variable vFiscalYrPeriod_TY will hold the sequence no for the period related to tofays's date.

tmpFYP:

load * resident FiscalCalendar where Date = today() ;

Let vFiscalYrPeriod_TY = peek('FiscalYrPeriodSeq', -1, 'tmpFYP') ;

drop table tmpFYP ;

Now you can use this variable in your set analysis expressions

To sum XXX for this payroll period use sum({<FiscalYrPeriodSeq={$(vFiscalYrPeriod_TY) }>} XXX)

To sum XXX for the prior payroll period use sum({<FiscalYrPeriodSeq={$(= $(vFiscalYrPeriod_TY) -1) }>} XXX)

To sum XXX for 13 periods prior use sum({<FiscalYrPeriodSeq={$(= $(vFiscalYrPeriod_TY) -13) }>} XXX)

Much easier than trying to calculate the periods from dates.

Re: Calendar with Pay Periods

jenmclean — Wed, 04 Mar 2015 20:56:02 GMT

This is still an issue, I tried the suggestions but it did not work for me. If anyone has any other suggestions, it would be greatly appreciated.