Hello, pleasei want my year to start with april in all my charts. Thank you
Perhaps one way would be to create a Fiscal Month dimension that takes your fiscal date/month and uses case statements to output the same string. The difference is that if you use case parameter instead of SQL CASE statement, Looker will honour the order.
Here is my test:
And then result of the ordering:
If you want multi-year timeseries that if you select “2 complete years” it will start from April 2019, then it’s much more complicated as you would have to create some sort of translation between calendar years and your fiscal calendar
@Dawid 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
@Dawid 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)
Then you have to write some sort of translation from the fields you have in filters to the actual data. I did something like that for custom weeks I needed, so when selecting last 2 complete weeks on Monday it would use Thu-Fri without the preceding Fri/Sat/Sun. But it’s just my assumption
@tatuspark - haven’t tested it, but this might help: https://docs.looker.com/reference/model-params/fiscal_month_offset
Then you have to write some sort of translation from the fields you have in filters to the actual data. I did something like that for custom weeks I needed, so when selecting last 2 complete weeks on Monday it would use Thu-Fri without the preceding Fri/Sat/Sun. But it’s just my assumption
Would you be able to share how you did this please? I have a use case very similar.
@Dawid Would you be able to share how you did this please? I have a use case very similar.
Let’s see if I remember. Here is what I would do in BigQuery.
Example data:
SELECT
calendar_date,
DATE_SUB(calendar_date, INTERVAL 3 MONTH) AS fiscal_date
FROM
UNNEST(GENERATE_DATE_ARRAY('2019-01-01', '2021-04-01', INTERVAL 1 MONTH)) AS calendar_date
Calendar date would be, let’s say, your date dimension in your data. The original. The fiscal date is only used to interact with data.
Now you could apply this:
On the fiscal date to change the labels. Fiscal date month number 1 is not January so you have to change it to April, which is slightly different than the screenshot but it’s just to reiterate the way to get the labels.
Now let’s say I use the filter “Fiscal date in last 2 complete years”. This will be translated to (simplified): WHERE YEAR(fiscal_date) IN(2019, 2020) right? Which means 2019-01-01 to 2020-12-01 inclusive.
But the actual data taken from the database, based on the translation will be 2019-04-01 to 2021-03-01.
The only problem is that you have to make sure people don’t use these things with date in rage, from, before etc. Unfortunately we can’t hide the timeframes filter options but when I did this, I made sure to let people know that it only works in specific way and they were okay with it.