Creating custom date ranges for display

user02830
user02830 Member
in Archive

I would like to display my cards with a custom date range that doesn't exist. I need financial year to date for Australia. Our financial year runs from 1 July to 30 June and so all our clients here would like their data displayed that way. How can I do this? There isn't an option in the date range field to do this.

 

Domo arigato!

 

Tagged:

Comments

  • Valiant
    Valiant Coach

    One way that you can accomplish this would be by using the date dropdown available for each card when in the Analyzer. 

     

    Just select the option for "Between" and then specify the date range you want to display. Please see below for refrence:image.png

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Let me know if you have any questions on this or if this still doesn't satisfy your needs.

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the thumbs up if this post helped you.

  • user02830
    user02830 Member

    Hi Valiant,

     

    Thanks for that but the problem is that I want to do something like Financial Year to Date. If I were to follow your suggestion, I would have to manually update the date each day. I would like something that does it automatically but with a customer year range. There exists a year to date, month to date, etc. But not a custom year to date.

     

    Any help is very much appreciated.

     

    Thanks,

    Evan

  • user02830
    user02830 Member

    Hi Valiant,

     

    Any update on the issue I am facing? Can anyone help out with this issue?

     

    Thanks!

  • Valiant
    Valiant Coach

    Evan,

     

    This sounds like a good case for something to submit to the idea center. Having additional options added for time periods or even customizable ones, would be very helpful to many people.

     

    Sincerely,

    Colt

  • user07995
    user07995 Member

    You can accomplish what you want by creating a custom date that shifts the date to fall within the fiscal year specified. Then strip the month/day information so that your dates show only 01-JAN and the fiscal year. This will prevent a little confusion. Then you can simply use the date range filter using the fiscal_year_date with this year/previous year/year to date, etc. Anything other than year related filters will not work correctly with this example. You will need to use a SQL DataFlow to accomplish this.

     

    Example:

    (Assuming 01-July-2016 is Fiscal Year 2017 and your original date field is called `original_date`)

     

    SELECT
        dt.original_date,
        dt.value,
        CONCAT('01-JAN-' , YEAR(DATE_ADD(dt.original_date, INTERVAL 6 MONTH))) fiscal_year_date

    FROM
        domo_test_dates_xlsx_sheet1 dt

     

    2017-09-22 16_27_22 - test_dates - Domo.png

    Note that 7/10/16 has been converted to 1/1/17 and would thus be included in a fiscal year 2017 filtered card.

     

    The other way to accomplish something like this is to simply use YEAR(DATE_ADD(dt.original_date, INTERVAL 6 MONTH)) and leave it as a quick filter on your card.

     

  • user02830
    user02830 Member

    Thanks for the idea user07995! 

     

    I'll implement it and see how I go.

  • DataMaven
    DataMaven Coach

    Another possibility is to use the Date Operations to get the default periods, based on calendar year, and then map each of those to the periods they would be in your fiscal year.  That would be something you could do without coding and in just a couple of steps.  

     

    Let me know if you need guidance and/or screenshots.  I am happy to help.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • MackDaddy
    MackDaddy Member

    @DataMaven  Can you show an example of this?  It would be very helpful

  • rado98
    rado98 Contributor

    Hi

     

    Ask your rep to set-up a fiscal calendar for your instance. They should have offered it to you on day one as it is very useful for things like this and you will get value out of it well beyond your current issue.

     

    Once that has been set-up the Date Range Selector on the standard calendar will do what you need it to do. You can even enable or disable it on a per card basis.

     

    Until it has been set-up one of the other proposed solution should work but I cannot stress enough that you should get the Fiscal Calendar set-up.

Categories