Fiscal Week of Year

[adstott90]

Hi all,

I’m working on a query at the moment and I want to compare the weekly revenue and customers with that of the previous year. Sounds straightforward, except that I want to compare weeks of the financial year (starts 01/04/2020) with last year. So essentially what I want the table to look like is something like this:

Week No. | FY 2020 Revenue | FY 2019 Revenue | Year on Year change |
1
2
3
4
etc.

The problem is I can’t seem to work out how to generate the week number of the financial year consistently - anyone done this previously or have any tips on where to start?

Thanks, Adam

[Dawid]

How are you going to deal with the fact that 1st April is now always your first day of the week?

[adstott90]

@Dawid That was half of the issue to be honest - it’s relatively straightforward to work out the date difference between two dates and work out whether they’re in week 1, 2 etc. based on 7-day periods after the start of the financial year, but that means that it’d look like what you’ve just rightly pointed out, which isn’t what I want.

What I’m basically looking for is to have it set up like the week_of_year dimension in Looker, where you might have week 0 or week 53 as incomplete weeks for instance - I don’t actually mind too much how we get to it as long as it’s consistent.

[rcronin]

I’ve gone back and forth on similar issues facing with the fiscal calendar. We use a 4-4-5 fiscal calendar. I found it wasn’t feasible to try to model it in LookML. Instead, I created a date dimension table in our database.

In your case here with a date dimension table, you’d just model up dimensions for fiscal week and year. Pivot on year and group by week. Then I’d assume you could use a table calculation for the year over year change.

[Dawid]

@adstott90 I always build some sort of calendar table first. That becomes my first point of truth of any aggregation, time-based analysis, etc.

But are you trying to do it exclusively in Looker or SQL?

If you want to use Looker’s dimension_group you’re going to have to “fake” the date. For example your dimension - week of year = 1 has to be translated in SQL into first week after 1st April that contains Thursday (going with ISO week_of_year logic here).

I have done something similar in the past

[adstott90]

@rcronin @Dawid

Nice suggestions guys in terms of using a calendar table - I haven’t used one before but to be honest from what I’ve read about them I think that could realistically be the best way here - as for Dawid’s question about SQL or Looker, I honestly don’t mind, as an end point I want to have it in Looker but I’m pretty much planning to build a table specifically for this report (I’ve already basically written the code for a table covering another aspect of the report) and then build a new explore for this stuff. Ryan thanks for the heads up with regard to modelling it in LookML, I know not to bother with that at least! The pivot on year and group by week option is basically what I’m trying to get to, that’s correct.

I’ll try and sort the calendar table and let you know how I get on!

Thanks again, Adam

[bkeyesSlack]

I know I am a little late to the party, but for every project I start for a new company it seems I need to create a new dates table. Here is the stock snowflake code I use to write the dates table. I use some parameters to reduce errors and set up the fiscal offsets. (This is helpful when you need additional dimensions that Looker doesn’t provide like fiscal week of year)

-- set the fiscal month offset
/*
set fiscal_month_offset = 11;
set end_date = TO_DATE('2031-01-31','YYYY-MM-DD');
set start_date = TO_DATE('2017-02-01','YYYY-MM-DD');
set num_days =  (Select datediff(day, $start_date, $end_date)+1); */

/*

With dates AS (
                -- we will use the date add, and the generator to make this work
               select
                      dateadd(
                        day,
                        '-' || row_number() over (order by null),
                        dateadd(day, 1, $end_date)
                      ) as full_date
                    from table (generator(rowcount => $num_days))

), 
            -- now that we have a generator and a list of dates we will set the periods 
            dates_periods AS (
                    SELECT full_date,
                           -- construct year and fy 
                           year(full_date) AS calendar_year,
                           year(dateadd('month', $fiscal_month_offset, full_date)) AS fiscal_year,
                           
                           -- calendar quarter and fiscal quarter
                           quarter(full_date) AS calendar_quarter,                                                                    
                           CONCAT(year(full_date),'-Q',quarter(full_date)) AS calendar_year_quarter,  
              
                           QUARTER(dateadd('month',$fiscal_month_offset,date_trunc('month', full_date))) as fiscal_quarter,                                         
                           CONCAT(year(dateadd('month', $fiscal_month_offset, full_date)),
                                  '-Q',
                                  quarter(dateadd('month',$fiscal_month_offset,date_trunc('month', full_date)))) AS fiscal_year_quarter,                            
                           
                           -- calendar month fiscal month
                           MONTH(full_date) AS calendar_month_num,                            
                           CONCAT(year(full_date),'-', lpad(month(full_date),2,'0')) as cal_month_year,
                           MONTH(dateadd('month',$fiscal_month_offset,date_trunc('month', full_date))) as fiscal_month_number,
                           CONCAT(year(full_date),'-', lpad(month(dateadd('month',$fiscal_month_offset,date_trunc('month', full_date))),2,'0')) as fiscal_month_year,                                         
                           MONTHNAME(full_date) as month_name,
              
              
                           -- calendar week num and fiscal week num
                           WEEKISO(full_date) as calendar_week_num,
                           CONCAT(year(full_date),'-', lpad(WEEKISO(full_date),2,'0')) as cal_week_year,
                           WEEKISO(dateadd('day', 2,dateadd('month', $fiscal_month_offset, full_date))) as fiscal_week_num, -- this is a bid dodgy
                           CONCAT(year(dateadd('month', $fiscal_month_offset, full_date)),'-', 
                                  lpad(WEEKISO(dateadd('day', 2,dateadd('month', $fiscal_month_offset, full_date))),2,'0')) as fiscal_week_year,
              
              
              

                           -- day stats
                           DAYOFWEEKISO(full_date) as day_of_week_num_iso,
                           DAYOFWEEK(full_date) as day_of_week_num,                          
                           DAYNAME(full_date) as day_name
                           
              FROM dates
), 

    -- now we grab first and last day for fliters
    fiscal_year_dates AS (
                            SELECT fiscal_year,
                                   min(full_date) as first_day,
                                   max(full_date) as last_day
                           from dates_periods
                           GROUP BY 1
),

    -- now we grab first and last day for fliters
    calendar_quarter_dates AS (
                            SELECT calendar_year_quarter,
                                   min(full_date) as first_day,
                                   max(full_date) as last_day
                           from dates_periods
                           GROUP BY 1
),
    -- now we grab first and last day for fliters
    fiscal_quarter_dates AS (
                            SELECT fiscal_year_quarter,
                                   min(full_date) as first_day,
                                   max(full_date) as last_day
                           from dates_periods
                           GROUP BY 1
),
    -- now we grab first and last day for fliters
    calendar_month_dates AS (
                            SELECT cal_month_year,
                                   min(full_date) as first_day,
                                   max(full_date) as last_day
                           from dates_periods
                           GROUP BY 1
), 

full_dates as (
                SELECT 
                      d_dates.full_date,
                      -- calendar year
                      d_dates.calendar_year,
                      date_from_parts(d_dates.calendar_year,'01', '01') as fist_day_of_calendar_year,
                      date_from_parts(d_dates.calendar_year,'12', '31') as last_day_of_calendar_year,
                      row_number() OVER (partition by d_dates.calendar_year order by full_date ASC) AS day_of_calendar_year_num,

                      -- fiscal year
                      d_dates.fiscal_year,
                      fiscal_year_dates.first_day as first_day_of_fiscal_year,
                      fiscal_year_dates.last_day as last_day_of_fiscal_year,
                      row_number() OVER (partition by  d_dates.fiscal_year order by full_date ASC) AS day_of_fiscal_year,                      
  
                      -- calendar quarter
                      d_dates.calendar_quarter,
                      d_dates.calendar_year_quarter,
                      calendar_quarter_dates.first_day as first_day_of_calendar_quarter,
                      calendar_quarter_dates.last_day as last_day_of_calendar_quarter,
                      row_number() OVER (partition by d_dates.calendar_year_quarter order by full_date ASC) AS day_of_calendar_quarter_num,
  
                      -- fiscal quarter
                      d_dates.fiscal_quarter,
                      d_dates.fiscal_year_quarter,
                      fiscal_quarter_dates.first_day as first_day_of_fiscal_quarter,
                      fiscal_quarter_dates.last_day as last_day_of_fiscal_quarter,
                      row_number() OVER (partition by d_dates.fiscal_year_quarter order by full_date ASC) AS day_of_calendar_quarter_num,
  
                      -- calendar month (first and last day of month same for fiscal and calendar months)
                      d_dates.month_name,
                      d_dates.calendar_month_num,
                      d_dates.cal_month_year,
                      calendar_month_dates.first_day as first_day_of_month,
                      calendar_month_dates.last_day as last_day_of_month,
                      row_number() OVER (partition by d_dates.cal_month_year order by full_date ASC) AS day_of_month_num,

                       -- fiscal month
                       d_dates.fiscal_month_number,
                       d_dates.fiscal_month_year,
                       

                       -- calendar week of year (day of week num same for fiscal and calendar week)
                      d_dates.calendar_week_num,
                      cal_week_year,
                      d_dates.day_of_week_num_iso,
                      d_dates.day_of_week_num,
                      d_dates.day_name,
  
                      -- fiscal week of year
                     d_dates.fiscal_week_num,
                     d_dates.fiscal_week_year
               

                FROM dates_periods as d_dates
                LEFT JOIN fiscal_year_dates on d_dates.fiscal_year = fiscal_year_dates.fiscal_year
                LEFT JOIN calendar_quarter_dates on d_dates.CALENDAR_YEAR_QUARTER = calendar_quarter_dates.CALENDAR_YEAR_QUARTER
                LEFT JOIN fiscal_quarter_dates on d_dates.fiscal_year_quarter = fiscal_quarter_dates.fiscal_year_quarter
                LEFT JOIN calendar_month_dates on d_dates.cal_month_year = calendar_month_dates.cal_month_year
)
SELECT *

            
FROM full_dates