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)
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!
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..
@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
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 😄
-