Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Fiscal week Monday-Sunday, running from April

I am trying to create a Fiscal Calendar where Fiscal Week runs from Monday - Sunday and starts in where 1st of April falls to.

So for example:

1st April 2015 was Wednesday, therefore 30th March - 5th April is Week 1.

1st of April can be any day from Monday - Sunday and where it falls this will be always Fiscal Week 1. Now, my calendar looks like this:

SET vFiscalYearStartMonth = 4;

Set vCal_FD = 1; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}

Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}

Set vCal_RD = 1; // Reference day = This day in Jan defines week one {1..7}

LET vStartDate =   NUM('04/03/2013');

LET vEndDate =Num('31/03/'& (Right(Today(),4)+1));

MasterCalendar:

  LOAD

  *

        ,Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter

        ,Dual(Month,FiscalMonth)

    AS FiscalMonthName;

  Load *,

          Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,

          Year( WeekYearRefDate ) as WeekYear;

     Load *,

          Date( YearStart( If( $(vCal_BW), Date, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate;

     Load *,

          Date( WeekStart( Date, 0, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate;   

  LOAD

  *

  ,YeartoDate(Date)*-1                                                                         AS CurYTDFlag 

  ,YeartoDate(Date,-1)*-1                                                                    AS LastYTDFlag 

  ,inyear(Date, Monthstart($(vEndDate)),-1)                                        AS RC12    

  ,WeekDay(Date)                                                                                AS WeekDay

  ,Day(Date)                                                                                          AS Day

  ,Year(Date)&'-'& week(Date)                                                             AS Year_Week

    ,Div(Date-YearStart(Date,0,4),7)+1 & '-' & WeekYear(Date)         AS Week_Year

  ,Week(Date)                                                                                      AS Week // Standard Calendar Week

    ,date(monthstart(Date), 'MMM-YYYY')                                           AS MonthYear 

  ,Mod(Month(Date), 13)                                                                      AS MonthNum // Standard Calendar Month Number

  ,Month(Date)                                                                                     AS Month // Standard Calendar Month

  ,Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3))                     AS Quarter // Standard Calendar Quarter

  ,Year(Date)                                                                                        AS Year // Standard Calendar Year

  ,Div(Date-YearStart(Date,0,4),7)+1                                                  AS FiscalWeek // Fiscal Week

  ,Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1                     AS FiscalMonth   // Fiscal Calendar Month

  ,YearName(Date, 0, $(vFiscalYearStartMonth))                               AS FiscalYear

    ,AutoNumber(YearName(Date, 0, $(vFiscalYearStartMonth)))      AS NumFiscalYear;   // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date

,RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Running this script I get the following:

Capture.PNG

However, the Fiscal Week 1 for 2015-16 should start on Monday 30th March.

Any suggestions?

I have had a look at the HC blog posts: Fiscal Year and this one Redefining the Week Numbers

4 Replies
Highlighted
Not applicable

Did you ever get any help on this?

Highlighted
MVP & Luminary
MVP & Luminary

Maybe this is helpful: Fiscal Calendar with Non-Standard Days (Not 1-31). You could find more informations here: How to use - Master-Calendar and Date-Values‌.

- Marcus

Highlighted
Contributor
Contributor

See attached, if you have yet to find a solution:  Custom Fiscal Calendar

Tyler

Highlighted

Hey, Did you got the Solution? Can you please post that and close this thread. Because, today whole day i am suffering for that. I want to start each week from Monday only after starting the New Year

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)