Calculate Week Num - Start Fiscal Year Aug 1st.

[JustinDallas]

Hello Folks,

I have dashboards with a Master Calendar.  In them, I use the whole "WeekNum" calculation.  But recently, I've been given a requirement that the Fiscal Year starts the "Week of August 1st".  Now for a single year, it's easy to just add/subtract the difference from the WeekNum(SomeDate) value.  But my calendar extends from 2010 -> Today().

How do I tell my calendar that when its calculating WeekNum, to use the week of August 1st as WeekNumb = 1?  Right now, this is the pseudo-code I've got going in my head right now.

LOAD *, 
If( CalDate > MakeDate(Year(CalDate),8,1),
    Week(CalDate) - Week(MakeDate(Year(CalDate),8,1),
          //Calc the difference in weeks below
        Interval(Floor(CalDate - MakeDate(Year(CalDate) - 1,8,1)), 'W')
        )

Any help is greatly appreciated.

[Anonymous]

Hi,

How about using

SET FirstMonthOfYear=8; - this will make start of the year from August.

SET FirstWeekDay=6;

SET BrokenWeeks=1; - you can use these two to set the week start day in that month.

Regards,

Sadasiva

[JustinDallas]

Hello Sada,

Sorry it took me so long to get back to you. Unfortunately, that doesn't seem to work.

SET FirstMonthOfYear=8;
Weeklings:
LOAD * Inline [
FirstDate
    '8/6/2017'
    '8/7/2017'
    '10/9/2017'
]
;


Weeklings2:
LOAD *, Week(FirstDate)
Resident Weeklings
;


DROP TABLE Weeklings
;
Exit Script
;

I would expect the dates 8/6/2017 to have a Week() value of 1 or two, but instead, the first week is still the week of Jan 1st.

[Anonymous]

Hi Justin,

This may helps.

Custom Fiscal Calendar

Regards,

Sadasiva

[JustinDallas]

This was my final answer.  It's kind of sloppy as you can tell by the fact I have to throw away negative Fiscal Weeks which shouldn't have been created in the first place.  But from what I can tell, it lines up.

The requirement for me was:  The first week of the fiscal year ends on the first Sunday in August.

Let varMinDate = Num(Makedate(2015,7,1));

Let varMaxDate = Num(Makedate(2020,12,31));

Let v2014 = Num(WeekStart((Makedate(2013,8,3))));

Let v2015 = Num(WeekStart((Makedate(2014,8,1))));

Let v2016 = Num(WeekStart((Makedate(2015,8,6))));

Let v2017 = Num(WeekStart((Makedate(2016,8,5))));

Let v2018 = Num(WeekStart((Makedate(2017,8,4))));

Let v2019 = Num(WeekStart((Makedate(2018,8,3))));

Let v2020 = Num(WeekStart((Makedate(2019,8,1))));

Let v2021 = Num(WeekStart((Makedate(2020,8,7))));

Let v2022 = Num(WeekStart((Makedate(2021,8,6))));

TempCalendar:

LOAD date($(varMinDate)+IterNo()-1) AS TempDate

AUTOGENERATE (1)

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate)

;

FiscalCalendar:

LOAD DISTINCT *

WHERE FiscalDateWeek > 0

;

LOAD

  WeekStart(FiscalDate) AS [Beginning Date],

  WeekDay(WeekStart(FiscalDate)) AS [Beginning Day],

  WeekEnd(FiscalDate) AS [Ending Date],

  WeekDay(WeekEnd(FiscalDate)) AS [Ending Day],

  If( Week(FiscalDate) = Week(TempStartingDate) AND Year(FiscalDate) = Year(TempStartingDate), 1

  ,

    If( Year(FiscalDate) = Year(TempStartingDate), Week(FiscalDate) - Week(TempStartingDate) + 1

    ,

      If( Week(YearEnd(TempStartingDate)) = 1,

        Week(YearEnd(TempStartingDate) - 7) - Week(TempStartingDate) + Week(FiscalDate),

        Week(YearEnd(TempStartingDate)) - Week(TempStartingDate) + Week(FiscalDate)

      )

    )

  ) AS FiscalDateWeek,

  Year(TempEndingDate) AS Year

;

LOAD *,

Date(If(FiscalDate >= $(v2014) AND FiscalDate < $(v2015), $(v2014),

If(FiscalDate >= $(v2015) AND FiscalDate < $(v2016), $(v2015),

      If(FiscalDate >= $(v2016) AND FiscalDate < $(v2017), $(v2016) ,

          If(FiscalDate >= $(v2017) AND FiscalDate < $(v2018), $(v2017),

              If(FiscalDate >= $(v2018) AND FiscalDate < $(v2019), $(v2018),

                  If(FiscalDate >= $(v2019) AND FiscalDate < $(v2020),  $(v2019),

                      If(FiscalDate >= $(v2020) AND FiscalDate < $(v2021),  $(v2020),

                              If(FiscalDate >= $(v2021) AND FiscalDate < $(v2022),  $(v2021))

)

)

)

)

)

)

), 'MM/DD/YYYY') AS TempStartingDate,

Date(If(FiscalDate >= $(v2014) AND FiscalDate < $(v2015), $(v2015),

If(FiscalDate >= $(v2015) AND FiscalDate < $(v2016), $(v2016),

      If(FiscalDate >= $(v2016) AND FiscalDate < $(v2017), $(v2017) ,

          If(FiscalDate >= $(v2017) AND FiscalDate < $(v2018), $(v2018),

              If(FiscalDate >= $(v2018) AND FiscalDate < $(v2019), $(v2019),

                  If(FiscalDate >= $(v2019) AND FiscalDate < $(v2020),  $(v2020),

                      If(FiscalDate >= $(v2020) AND FiscalDate < $(v2021),  $(v2021),

                              If(FiscalDate >= $(v2021) AND FiscalDate < $(v2022),  $(v2022))

)

)

)

)

)

)

), 'MM/DD/YYYY') AS TempEndingDate

;

LOAD

  TempDate AS FiscalDate,

  Year (TempDate) AS FiscalYear,

  Month (TempDate) AS FiscalMonth,

  Day (TempDate) AS FiscalDay,

  Week(TempDate) AS FiscalWeek

Resident TempCalendar

Order by TempDate ASC;

STORE FiscalCalendar into [lib://Qlik Data (dts_qlikservice)/Test/PFWeeks.csv] (txt);

EXIT Script

;

[JustinDallas · Accepted solution]

The above is wrong, as you can end up with a Week 73.  Below is the correct solution (for now)

Let varMinDate = Num(Makedate(2013,12,19));
Let varMaxDate = Num(Makedate(2020,1,3));


//Find all the '1st Weeks' that 
//we will be calculating over
Let v2014 = Num(WeekStart((Makedate(2013,8,3)))); 
Let v2015 = Num(WeekStart((Makedate(2014,8,1)))); 
Let v2016 = Num(WeekStart((Makedate(2015,8,6)))); 
Let v2017 = Num(WeekStart((Makedate(2016,8,5)))); 
Let v2018 = Num(WeekStart((Makedate(2017,8,4)))); 
Let v2019 = Num(WeekStart((Makedate(2018,8,3)))); 
Let v2020 = Num(WeekStart((Makedate(2019,8,1)))); 
Let v2021 = Num(WeekStart((Makedate(2020,8,7)))); 
Let v2022 = Num(WeekStart((Makedate(2021,8,6)))); 


TempCalendar:
LOAD date($(varMinDate)+IterNo()-1) AS TempDate ,
     Week($(varMinDate)+IterNo()-1) AS StandardWeek,
     WeekStart($(varMinDate)+IterNo()-1) AS StandardWeekStart,
     WeekEnd($(varMinDate)+IterNo()-1) AS StandardWeekEnd,
     Year( WeekStart($(varMinDate)+IterNo()-1)) AS StandardYear
AUTOGENERATE (1) 
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate) 
;


NoConcatenate
FiscalCalendar: 
LOAD DISTINCT StandardWeek,StandardWeekStart,StandardWeekEnd,StandardYear
Resident TempCalendar 
Order by TempDate ASC; 


DROP TABLE TempCalendar
;


FiscalCalendar2:
LOAD StandardWeekStart AS "Beginning Date",
WeekDay(StandardWeekStart) AS "Beginning Day",
     StandardWeekEnd AS "Ending Date",
     WeekDay(StandardWeekEnd) AS "Ending Day",
     [PF Week Num] AS FiscalDateWeek,
     [PF Year] AS Year
;
LOAD *, Floor((StandardWeekStart - StartingFencePost) / 7) + 1 AS [PF Week Num],
Year(StartingFencePost) + 1 AS [PF Year]
;
LOAD *, IF(StandardWeekStart < '$(v2014)', '$(v2013)',
IF(StandardWeekStart < '$(v2015)', '$(v2014)',
            IF(StandardWeekStart < '$(v2016)', '$(v2015)',
            IF(StandardWeekStart < '$(v2017)', '$(v2016)',
            IF(StandardWeekStart < '$(v2018)', '$(v2017)',
                        IF(StandardWeekStart < '$(v2019)', '$(v2018)',
                            IF(StandardWeekStart < '$(v2020)', '$(v2019)',
                                IF(StandardWeekStart < '$(v2021)', '$(v2020)')
                    )
)
)
)
    )
)
)                        AS StartingFencePost
Resident FiscalCalendar
ORDER BY StandardWeekStart
;


DROP TABLE FiscalCalendar
;
RENAME TABLE FiscalCalendar2 TO FiscalCalendar
;




STORE FiscalCalendar into [lib://Qlik Data (dts_qlikservice)/Test/PFWeeks.csv] (txt);


EXIT Script 
;