Question

Fiscal Month Offset Parameter 4.8+

  • 28 February 2017
  • 17 replies
  • 264 views

Beginning with Looker 4.8, we introduced a new model level parameter called fiscal_month_offset:. This will allow you to define when your fiscal year begins for each model if you don’t use the calendar year for your quarters. To set your fiscal year, navigate to your model file and include this parameter like so:


New LookML


connection: thelook            

### INCLUDES ###
include: "*.view.lkml"
include: "*.dashboard.lkml"

fiscal_month_offset: 1



Old LookML

- connection: thelook            

### INCLUDES ###
- include: "*.view.lookml"
- include: "*.dashboard.lookml"

- fiscal_month_offset: 1


Here, we defined our offset to be 1 month and assuming the calendar year starts in January, our new fiscal year will begin in February and our fiscal Q1 will include February, March, and April.


##Fiscal Year Timeframes

We also introduced four new fiscal timeframes for the dimension_group field:


fiscal_year, fiscal_quarter, fiscal_month_num, and fiscal_quarter_of_year


####New LookML


dimension_group: created {
type: time
timeframes: [date, month, year, quarter, fiscal_year, fiscal_quarter, fiscal_month_num, fiscal_quarter_of_year]
sql: ${TABLE}.created_at
}



Old LookML

- dimension_group: created
type: time
timeframes: [date, month, year, quarter, fiscal_year, fiscal_quarter, fiscal_month_num, fiscal_quarter_of_year]
sql: ${TABLE}.created_at


What is really cool is that the fiscal_month_offset: parameter only affects the fiscal_* timeframes. So if you’d like, you can have your fiscal and calendar timeframes all in one dimension_group like above. As you can see below, when we have our offset set at 1, January appears in Q4 for our fiscal quarter but Q1 in the calendar quarter.



##Filtering

If you wanted to use matches advance on one of those timeframes, as of now, we introduced two new intervals, fiscal year and fiscal quarter. For example, we can write something like 2 fiscal years ago to grab data from 2 fiscal years ago, like so:



17 replies

Where have you been all my life fiscal_month_offset? My companies fiscal year starts in October. Simple, yet extremely valuable feature!

How you can use this when fiscal year starts on first Sunday of the week, when Feb starts. So for 2017, it will select Jan 29 2017 as first day of the year. How can I do that using fiscal_month_offset: 1? Also week always start on Sunday so adding week_start_day: sunday in my explore.

Month-level timeframes for calendar year include a ‘month_num’ timeframe, which includes only the calendar month number, as well as a ‘month’ timeframe, which includes both calendar year and calendar month number. Month-level timeframes for fiscal year include only a ‘fiscal_month_num’ timeframe. Do you anticipate adding a fiscal_month timeframe?

Userlevel 5
Badge

@amock we initially decided not to add a fiscal_month timeframe since that isn’t commonly used as far as we knew. However, if this is actually common or something you or others would like to see, we can easily add this as another fiscal timeframe!

Userlevel 5
Badge

@kshah7 we don’t currently have a way to start a fiscal year on any other day besides the first day of the month. This type of transformation is very hard to deal with in SQL, and we haven’t figured out a good way to make that possible. But I’ll pass your ask on the our product team!

Yes, fiscal_month is something we’d like to see…thx!

hey @amock, I’ll pass that along to the product team! Thanks for the feedback!

Userlevel 1

@kshah7, this may help. This is the starting point for a 4-4-5 calendar, always beginning the Sunday of the week that contains August 1. We have a date dimension table, containing most key date parts like year, week number, etc., and a basic date field called sql_date.The other key field from our table in this solution is week_start_date. If you don’t have a table, you can pretty easily get the SQL in your dialect to derive the week start. Looker’s date dimension groups are great in that they’re very easy to use, but because this 4-4-5 technique uses a join, it works a whole lot better with a pre-defined date dimension table.


I then persist this derived table, and build all the 4-4-5 calendar dimensions from this starting point.


with week_aug1_start as (select week_start_date, year from date_dim where day = 1 and month = 8)


select d.*,
case when d.sql_date < y2_445_aug.week_start_date
then y1_445_aug.week_start_date
else y2_445_aug.week_start_date
end as fy_445_aug1_start_date,
from date_dim d
inner join week_aug1_start as y1_445_aug
on d.year-1 = y1_445_aug.year
inner join week_aug1_start as y2_445_aug
on d.year = y2_445_aug.year

So does this option gives any advantage over having Fiscal calander table in Database (which we already have, only thing we are not using any of the diemnsion_group options)?

hey @kshah7, the main advantage is that you can define when your fiscal year begins on a model to model basis if you don’t follow the traditional calendar year for your quarters. So, even if you do have a fiscal calendar table in your database, you can use this parameter to denote the number of months your fiscal year is offset from the normal calendar year!

Userlevel 5
Badge

@kshah7 Using Looker’s fiscal_month_offset also gives some benefits for filtering! If you specify a fiscal_month_offset in your model, then you can filter based on fiscal years and fiscal quarters (these options will appear in the filter dropdowns for the fiscal_year and fiscal_quarter timeframes). So you can filter something like in the past 2 fiscal years, which was more difficult to do before.

How about if you have multiple fiscal years? For example, our data contains multiple clients in one model And they have different fiscal year start. Do I have to build a model for each fiscal start?

Userlevel 2

Hey @Jovelyn_Saldana,


There’s currently no way to do this, so using a separate model for each fiscal year is the best option for now. I’ll let the product team know that it would be helpful to have multiple fiscal years in a single model.

I am piggy-backing on the request of @Jovelyn_Saldana . We also have multiple clients with multiple fiscal years. It would be incredibly useful to have a variable for fiscal_month_offset.


We have clients with looks / dashboards that are mature, and they will not work if we switch them to an entirely new model just to change one setting, so it means a lot of re-work. Reading a variable user attribute is one way I can think of to implement this, much like the access_filters. The model LookML might look something like:


fiscal_month_offset: {user_attribute: my_fiscal_month_offset}


please and thank you - this would be amazingly useful!

@davehoy27 I managed to find a work around for this. First, I had a client table where it contains our clients and their corresponding fiscal start month, and name that field FYStartMonth.


In our client view, I created a dimension for fy_start_month:

dimension: fystart_month {

label: “Fiscal Year Period”

type: number

hidden: yes

sql: case when ifnull(${TABLE}.FYStartMonth,1) = 1 then 0 else (13 - ${TABLE}.FYStartMonth) end;;

}


Then all my date dimension groups have a separate fiscal time dimension:

dimension_group: fy {

type: time

label: “Date - FY”

timeframes: [

quarter_of_year,

month_num,

year

]

sql: cast(DATE_ADD(cast(${TABLE}.Date as date), INTERVAL ${client.fystart_month} MONTH) as timestamp);;

}


Hope this helps.

@Jovelyn_Saldana, very nice! Thank you for the suggestion, I will work through that here.

Hello  @Vincent, @chris.seymour, @allegra, @lindsey  I want to compare the months of 2 years (year n vs year n-1) and display the results for each month starting with April which is the start of my fiscal year (april to march).Thanks

 

Reply