Question

fiscal month

  • 17 February 2021
  • 8 replies
  • 57 views

 

Hello, pleasei want my year to start with april in all my charts. Thank you

 

 


8 replies

Userlevel 6
Badge

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_Nawrot 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_Nawrot  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)

 

Userlevel 6
Badge

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

Userlevel 2
Badge

@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_Nawrot  Would you be able to share how you did this please? I have a use case very similar.

Userlevel 6
Badge

@rcronin @tatuspark 

 

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. 

 

Reply