fiscal month

tatuspark
Participant IV

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

0b92fd82-7b66-4ac4-af8c-9312f6e18f5c.png
0 8 910
8 REPLIES 8

Dawid
Participant V

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:

be2da167-cd50-472a-88fb-0c00ced119ab.png

And then result of the ordering:

6b493621-6143-4a02-b24d-81b517ed958a.png

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

tatuspark
Participant IV

@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

tatuspark
Participant IV

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

b84875aa-8cb0-4968-b912-60478bb35162.png
132179ca-5f4f-4e84-9140-798e48fcad95.png

Dawid
Participant V

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

David_P1
Participant V

rcronin
Participant I

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.

tatuspark
Participant IV

@Dawid  Would you be able to share how you did this please? I have a use case very similar.

Dawid
Participant V

@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
9d690165-e962-4c03-a6bc-61f6ba47195d.png

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:

7d1f8e5b-c12a-4020-a69b-82ec6a4c5b19.png

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. 

Top Labels in this Space
Top Solution Authors