Week Start - Sunday instead of Monday

[jenmclean]

I found the attached script from Henric Cronström that accomplishes what I need to do so that I calculate weeks to match pay periods but am having an issue where it is generating a week 0 which should be Week 52 and it shows the first three days of that week as 0 and the remaining 4 as week 1. (see screen shot).Henric Cronström

How do I correct this? I am attaching my modified file as well.

[mbm]

No problem at all. I checked the current week flags and I had to adjust for the offset for Sunday instead of Monday. I also added the FPW flag which gets you the same week last year.

Take a look.

[jenmclean]

That works!

Then the last thing I need is:

Current Pay Period  - which will include all the weeks in current year pay period (CYPP)

LastYearPayPeriod - last's year corresponding pay period (LYPP)

I need to create a chart that is for Period X (Current Period) and includes Week 1,2,3, & 4.

If we are in Week 1 of the Current Period, then it will only show this week's revenue and weeks 2,3,4 will be blank until that week occurs.

[mbm]

Updated to include flags for Pay Period and to date flags for week as well.

[jenmclean]

Matt,

Sorry to be such a pain but what I need as well is 4 flags for:

CPW1 - CurrentPeriod Week 1

CPW2 - CurrentPeriod Week 2

CPW3 - Current Period Week 3

CPW4 - Current Period Week 4

[jenmclean]

Also my PPXX is off by a week.

PP12 should start on 11/10/13, currently showing it as 11/3/13

[mbm]

Attached is updated to show PP1 (Pay Period 1) beginning with Week 1 instead of Week 52 to fix the issue with the Pay Period dates. 2013 PP12 now shows start date of 11/10/2013.

I also added what I believe you are looking for regarding the weeks 1-4 for current pay period. I created a field called CPPW (Current Pay Period Week) which contains the values Week 1, Week 2, ... that can be used as the dimension for your chart.

I also added an Example Chart which shows the chart using CPPW as the dimension along with the CPTD (Current Period To Date) flag to limit the display to only the applicable weeks. My expression: Sum(1 * CPTD), is simply using the literal 1 as a value since there is nothing but calendar data in the file. Just change to whatever your expression needs to be e.g. Sum(PaycheckAmount * CPTD)

[jenmclean]

Matt,

I think we have it except for one thing. When I place a calendar object, I am off by one day.

1/1/2011 is supposed to be on Saturday and in the Calendar I show it starting on a Friday.

See attached.

Jennie

[mbm]

Jenny,

Unfortunately the Calendar object within QlikView is stuck being ordered from Monday - no way to change that, so you ARE seeing 1/1/2011 show up on a Saturday correctly. There are workarounds to this, but it takes a bit more to get it working than a regular Calendar object does. I've attached an example showing a custom calendar object which starts from Sunday. One thing to notice with the custom calendar is that you won't see the day numbers for the months before and after the month you're viewing on the calendar grid. Look at how the actual calendar object shows you the Dec 10 dates and Feb 11 Dates.

custom calendar doesn't show those - BUT it does have the Day of the week nicely displayed so there is no confusion:

Full credit to John Witherspoon for creating the custom calendar object which I pulled from this thread: http://community.qlik.com/thread/12623

I modified his example slightly to use native triggers and added a few new triggers to set the default year and month of the calendar popup if the date value is changed outside the calendar. That way whenever you open the calendar popup you'll see the current selected date highlighted.

The example requires a few steps to work into your application.

1. Use the latest script to generate the actual calendar. I had to add a field for ActualYear, since the Year field in your calendar is a Fiscal year.
2. Create the Data island to conditionally show and hide the calendar pop-up and another island which is used for the calendar popup view. (These are both in a new tab in your load script, you would just need to copy them into your app)
3. Create Document triggers on the new CWeekday and CWeekstart fields. These triggers set the Date on the actual calendar to match the Date value from the data island (CDate) based on your selection in the calendar pop-up. Those triggers also clear the Date island fields.
   1. Navigate to Settings>Document Properties>Triggers Tab>Field Event Triggers
   2. Copy the OnSelect triggers for the CWeekday, CWeekstart
4. Create Trigger on the "Show Calendar?" field to assign the Year and Month values in the Date Island so the popup defaults to the current month and year you have selected in the Actual Calendar
   1. Navigate to Settings>Document Properties>Triggers Tab>Field Event Triggers
   2. Copy the OnSelect triggers for the "Show Calendar?" fields.
5. The Month and Year in the data island table (CMonth and CYear) need to be set to Always One Selected value.
   1. Make sure a value is selected for month and year in the calendar popup.
   2. Right click the month and year fields.
   3. Navigate to Properties>Presentation Tab.
   4. Select CMonth or CYear  in the fields list
   5. Click the checkbox "Always one selected value"
6. I've set up the data island for the calendar object to contain dates from 1/1/1995 through 1/1/2025. If you want to change the available date range for the calendar object, make sure that you begin with a Sunday, otherwise your calendar popup won't be ordered properly.

[jenmclean]

Matt,

Thank you so much for all your help, it is very much appreciated!

I'd send you chocolate if I could!

Jennie

P.S. Hopefully this will help others that may have the same type of issue I do.

[jenmclean]

Matt,

Wondering if you could assist one more time on this. The Period Flags are not showing the correct dates. If I click on CP for Current Period I get dates of 1/6/2013 - 2/2/2013. The correct dates should be 12/8/2013 - 1/4/2014 as I am currently in Week 51 of the calendar. Would you have a chance to look at this?