5 Replies Latest reply: Aug 4, 2017 3:10 PM by Justin Dallas

# Calculate Week Num - Start Fiscal Year Aug 1st.

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.

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.

• ###### Re: Calculate Week Num - Start Fiscal Year Aug 1st.

Hi,

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,

• ###### Re: Calculate Week Num - Start Fiscal Year Aug 1st.

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

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

Weeklings2:
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.

• ###### Re: Calculate Week Num - Start Fiscal Year Aug 1st.

Hi Justin,

This may helps.

Custom Fiscal Calendar

Regards,

• ###### Re: Calculate Week Num - Start Fiscal Year Aug 1st.

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:

AUTOGENERATE (1)

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

;

FiscalCalendar:

WHERE FiscalDateWeek > 0

;

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

;

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

;

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

;

• ###### Re: Calculate Week Num - Start Fiscal Year Aug 1st.

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:
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:
Resident TempCalendar
Order by TempDate ASC;

DROP TABLE TempCalendar
;

FiscalCalendar2:
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
;