Creating custom date ranges for display

user02830

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!

 

BlueRooster

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:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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

Hi Valiant,

 

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

 

Thanks!

BlueRooster

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

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

 

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

Thanks for the idea user07995! 

 

I'll implement it and see how I go.

DataMaven

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.  

MackDaddy

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

rado98

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.