Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Specialist II
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
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:

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

;

View solution in original post

5 Replies
Highlighted
Creator II
Creator II

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

Highlighted
Specialist II
Specialist II

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.

Highlighted
Creator II
Creator II

Hi Justin,

This may helps.

Custom Fiscal Calendar

Regards,

Sadasiva

Highlighted
Specialist II
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:

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

;

Highlighted
Specialist II
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:

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

;

View solution in original post