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: 
Not applicable

Fiscal Week query

Hi All,

I am looking to craete a Fiscal Week field but unsure how to do this.

Basically, my fiscal year starts on July 1st each year..

I want to create a new field for the correct fiscal Week, so currently we are in week 9 of the standard calendar, but out fiscal week is week 35.

I have the following example which i found on this community but i wanted the calendar to self generate instaed of hardcoding the start and end date into it as i don't want to have to remember to update the variables at the end of each year.

LET varMinDate = Num(MakeDate(01/07/2012));

LET varMaxDate = Num(MakeDate(30/06/2013));

LET varToday = Num(Today());

//************TempCalendar***********

TempCalendar:

LOAD

          $(varMinDate) + rowno() - 1 AS DateNumber,

          date ($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

          $(varMaxDate) - $(varMinDate) + 1;

 

//*************Master Calendar************

FiscalCalendar:

LOAD

          TempDate AS FiscalDate,

          Week (TempDate)-Week($(varMinDate))+1 AS FiscalWeek,

          Year (TempDate) AS FiscalYear,

          Month (TempDate) AS FiscalMonth,

          Day (TempDate) AS FiscalDay

Resident TempCalendar

Order by TempDate ASC;

 

Drop Table TempCalendar;

Can anyone help please?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

so currently the week starts on a saturday, but when the new financial year starts this year the week will start on a  so this year the week starts on a monday, next year it will start on a tuesday etc.

July 1st 2012 was a Sunday, wasn't it (not a saturday)?

So if I understood your request properly (week start will shift over years), it's just

     Div(TempDate-YearStart(TempDate,0,7),7)+1 as FiscalWeek

View solution in original post

8 Replies
Gysbert_Wassenaar

Try:

Week(addmonths(TempDate,6)) AS FiscalWeek


talk is cheap, supply exceeds demand
Not applicable
Author

Hi,

Where abouts should i put that?

Not applicable
Author

I can get this working in a text box using

Week(addmonths(TempDate,6))

which displays the correct fiscal week number, but only if i select a date. I could use this in a variable but how do i get the variable to populate without the need to select a date first?

any help will be appreciated.


Not applicable
Author

i think i have it..

=Week(addmonths(Today(),6) )

swuehl
MVP
MVP

Gysbert, if you do it like this, I think your weeks will have different number of days (up to 10) and therefore will not all start on Mondays.

[edit:

The problem is that months don't have a constant length, so addmonths(Date,6) will map e.g. all the dates 28.,29., 30.  and 31.8.2012  to 28.2.2013. There is an optional mode argument for addmonths() function to derive the calculated day from the end of month, but I can't see how this would be helping us here.]

timmyhop, you'll need to decide how you want to number your weeks (there are several standards, e.g. US and ISO that differ in week start day as well as week number starting trigger), then for example one way (Weekstart Mo, week numbering start with Jul 1st) would be to use something like:

    Div(TempDate-YearStart(TempDate,0,7) + WeekDay(YearStart(TempDate,0,7)) +7,7) as FiscalWeek

I am not saying that this is what you want (because you haven't specified your week numbering system), but I think it's returning a consistent number.

(use this line in your load script, in your fiscal calendar).

Message was edited by: swuehl

Not applicable
Author

Hi Stefan,

The week start day will differ each year as the year starts on the 1st july no matter what day it is. so currently the week starts on a saturday, but when the new financial year starts this year the week will start on a  so this year the week starts on a monday, next year it will start on a tuesday etc.

Does that make sense?

I am not sure what you mean by week no starting trigger?

Week 1 is from 1st july until 7th july, week 2 8th july until 14th july etc.

Does that help?

swuehl
MVP
MVP

so currently the week starts on a saturday, but when the new financial year starts this year the week will start on a  so this year the week starts on a monday, next year it will start on a tuesday etc.

July 1st 2012 was a Sunday, wasn't it (not a saturday)?

So if I understood your request properly (week start will shift over years), it's just

     Div(TempDate-YearStart(TempDate,0,7),7)+1 as FiscalWeek

Not applicable
Author

my mistake, it was a sunday! i didnt look at the calandar properley!

Thanks for your help