Fiscal periods in one report

Options
Jones01
Jones01 Contributor
in Charting & Analyzer

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

Answers

  • RobSomers
    RobSomers Coach

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • Jones01
    Jones01 Contributor

    @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
    MichelleH Coach

    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
    Jones01 Contributor

    @MichelleH Thank you.

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


    image.png


    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
    ST_Superman Domo Employee

    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
    Jones01 Contributor

    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
    jaeW_at_Onyx Coach

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

    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"
  • Jones01
    Jones01 Contributor

    @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

Categories