Fiscal periods in one report

Jones01 · 2022-08-25T07:28:25+00:00

There was an error rendering this rich post.

Hi,

I am looking to produce a simple report with the following headers

Date | YTD Value | Last Week Value

I have a dataset linked to my own fiscal dataset that has information for week start, year start, week number, period etc.

I am slightly confused on how to write a beast mode for the above as using the built in current_date function and year functions won't return the year based on my custom fiscal year.

How do people get around this?

This is a common use case for our customers.

Thanks

Find more posts tagged with

Accepted answers

All comments

RobSomers

@Jones01 In your ETL, you'll probably want to create a formula to calculate the fiscal year (could also do this in a beast mode depending on use case). You would just write a formula like this for each year that you need:

case when 'date' >= '2022-03-01' and 'date' < '2023-03-01' then 2022

when 'date' >= '2021-03-01' and 'date' < '2022-03-01' then 2021

...

end

There is probably a way to write this to be more dynamic without having to do each individual year, but this would be my solution.

Jones01

@RobSomers Thanks.

Unfortunately that won't work as the dataset will be linked to different fiscal years so the solution needs to be flexible.

If domo made the equivalent date functions like YEAR etc for the internal fiscal year we have uploaded to each subscriber instance I think it would be a piece of cake.

MichelleH

Hi @Jones01 if you haven't done so already, I suggest structuring your fiscal calendar dataset like the Domo Dimensions Calendar dataset (https://domohelp.domo.com/hc/en-us/articles/360042931454-Domo-Dimensions-Connector). Then you can join to your fiscal dataset by date, to add columns for fiscal year, period, and week. If you create a separate branch of the same ETL, to filter the fiscal calendar dataset to the current date, then you can join that to create columns for the current fiscal periods.

These ETL changes will allow you to mimic most beast mode date functions. @GrantSmith has done a pretty extensive write-up of how to create these PoP comparions: https://dojo.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

Jones01

@MichelleH Thank you.

Yes I have my own date dimension representing the fiscal year joined to my data.

but I still don't get how based on the date today I can calculate the dates for YTD.

so at the moment it would get where the Year Column = 2022 but it would need to be able to do this if the date today was the 31st December 2021 as that falls into that fiscal year.

I have used the PoP stuff by @GrantSmith and it is really good.

There is probably a simple solution to this that I have missed.

ST_Superman

If you are only looking to add YTD, you could add that as an extra field in your dataflow when you combine the fiscal calendar information. Something like:

YTD

CASE WHEN `FiscalYear`=2022 and `DateField`<=CURDATE() THEN 'TRUE' ELSE 'FALSE' END

You would need to change the 2022 value when the year turned over. This would allow you to just add a filter to your page or card where YTD = TRUE

Jones01

found a solution in the end.

As I pull the fiscal year data from our source db each day I have added what the period starts are based on the day it is pulling the data.

So my beast modes look like this

sum(case when `Report Date` >= `Current Year Start Date` and `Report Date` <= `Current Last Week End Date` then `Value` end)

Thanks for everyone's input.

jaeW_at_Onyx

@Jones01 i know i'm coming late to the party, but the long and short of it is, you can't use any date_part functions (year, month week) etc with a fiscal calendar, because as you've figured out, there is no way for the YEAR() function to know the span of your year.

if you wanted to be able to do FY_Year and FY_Month comparisons, you'd need a set of columns

current_date

current_fy_month

current_fy_year

(which could just be SELECT * from FY_Dimension where db_date = today())

then just CROSS JOIN that onto your transaction tables using a VIEW.

then in beast modes you can do calcs like

case when trans_fy_month = current_fy_month

which avoids using any of Domo's date_part functions.

Jones01

@jaeW_at_Onyx Thanks. Yes that is what I ended up doing but just did it in our source database and brought that over to domo.

Hopefully functions like TY_Year get added soon.

Thanks