Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor 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
JustinDallas
Valued Contributor II

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:

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

;

5 Replies
sadasiva
Contributor

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

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
Valued Contributor II

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

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

;

CaptureWeekMove.PNG

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.

sadasiva
Contributor

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

Hi Justin,

This may helps.

Custom Fiscal Calendar

Regards,

Sadasiva

JustinDallas
Valued Contributor II

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:

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
Valued Contributor II

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:

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

;

Community Browser