Dynamic Fiscal Year Offset

[RobTom1]

We are using Looker embedded in our app to service multiple clients.   Though most clients' use a calendar year, we have several that use a fiscal year and each of these clients may have a different fiscal year offset.  Is anyone aware of a way to set the fiscal year offset dynamically in LookML using client or user configuration/permissions?    

[dsimeonova]

Hey @RobTom1 !

Currently it's not possible to set dynamic value to the fiscal_month_offset parameter.
There are 2 options. 
1. To use separate model for each client (which depending on your set up can be difficult or just can make no sense).
2. To create your own "fake" fiscal year dimension like this (using BQ as dialect):

## 1.Create a fiscal_month user attribute and assign a value for each group/users (related to each company). Left as default the 0.

### 2. Create the Time dimensions ###
  dimension_group: created {
    type: time
    timeframes: [
      raw,
      time,
      date,
      hour_of_day,
      day_of_week_index,
      day_of_week,
      day_of_year,
      week,
      week_of_year,
      month,
      month_name,
      month_num,
      quarter,
      year,
      day_of_month
    ]
    sql: ${TABLE}.created_at ;;
  }

  dimension_group: created_fiscal {
    label: "Fiscal"
    group_label: "Created Date"
    type: time
    timeframes: [
      month_name,
      month_num,
      month,
      quarter,
      year
    ]
    sql: cast(DATE_ADD(cast(${TABLE}.created_at as date), INTERVAL cast({{_user_attributes['fiscal_month']}} as INT64) MONTH) as timestamp);;
  }

The only thing to have on mind when setting the filters is that you need to select inside of the viz the created_fiscal dimensions if you want to see it using the fiscal month value offset AND filter using the created_date dimension in order to make it work properly.

Hope this helps! Please share your thoughts afterwards 😉 

Regards!!