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

    Calculate Week Num - Start Fiscal Year Aug 1st.

    Justin Dallas

      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.

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

          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

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

              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.

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

                  Hi Justin,

                   

                  This may helps.

                   

                  Custom Fiscal Calendar

                   

                  Regards,

                  Sadasiva

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

                      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

                       

                       

                      ;

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

                          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 
                          ;