Fiscal periods in one report
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.
@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
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
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.0
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#latest1
@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.0
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:
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
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
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.1
@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
(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.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
@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.
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 195 Visualize
- 253 Beast Mode
- 2.1K Charting
- 11 Variables
- 81 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive