# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Specialist II

## 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.

```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.

1 Solution

Accepted Solutions
Highlighted
Specialist II

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
;
```
5 Replies
Highlighted
Creator II

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,

Highlighted
Specialist II

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.

Highlighted
Creator II

Hi Justin,

This may helps.

Custom Fiscal Calendar

Regards,

Highlighted
Specialist II

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

;

Highlighted
Specialist II

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
;
```