fiscal month order by month

[tatuspark]

Hello, I want to compare the months of 2 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)

[David_P1]

Hi @tatuspark 

Shared this link in your other post - give it a try 

https://community.looker.com/lookml-5/fiscal-month-23082?postid=43125#post43125

Looker offers an easy solution to your use case. You need to add the following parameter into your model (offset 3 if you want the fiscal year to start in April):

fiscal_month_offset: 3

Now all you need to do, is add the desired fiscal intervals into your date dimension_group. For example:

 dimension_group: date_name {
  type: time
  timeframes: [raw, date, month, quarter, year,
               fiscal_year, fiscal_month_num, fiscal_quarter]
  sql: ${TABLE}.date ;;
  datatype: epoch
  convert_tz: no
  }

Then you could pivot the fiscal year in your explore to compare both years against the fiscal_month_num. Similar to how you would do it for normal date dimensions.

Good luck!

[Dawid]

I didn’t even know this existed! I think it would be even more useful it this parameter could be applied on a dimension level. @David_P1 what do you think?

@tatuspark but with this parameter + extra timeframes, the translation I talked about in the previous post is basically done for you.. 

[David_P1]

@Dawid Agreed, it would allow for more flexibility - but prob also open the door for more inconsistencies, don’t you think? I can’t think atm of a scenario where having +1 fiscal year logic would be needed within a single model (in 99% of the cases I think I would apply the same logic to all date dimensions for that model). 

If I manage different accounts, then probably each has its own model, and a diff fiscal year logic could be applied then. But that’s my 2 cents… in any case, it’s nice that the parameter is available out-of-the-box 

[Dawid]

I combine a lot of financial data in all entities I deal with and let’s say you have orders view with created_at dimension group. In this case I don’t even want people to be bothered with looking at fiscal date/month/year. However, what if I have an invoice date,  that I would like the financial team to use. They could use fiscal timeframes and filtering but I wouldn’t put it in different model. 

Maybe not the best example but it was just from the top of my head 😄

[David_P1]

-