Question

fiscal month order by month

  • 17 February 2021
  • 5 replies
  • 51 views

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)

 


5 replies

Userlevel 2
Badge

Hi @tatuspark 

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

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!

Userlevel 6
Badge

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_P 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.. 

Userlevel 2
Badge

@Dawid_Nawrot 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 :slight_smile:

Userlevel 6
Badge

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 :D

Userlevel 2
Badge

-

Reply