Creating custom year to date default range

khoang

Hi, I'm was wondering how to create a custom year to date default range without having to set any filters from a user experience.  I wanted to take advantage of the 'This year' drop down feature logic that is built in, but is there a way to automatically default select 'This Year' rather than 'All time range'  so that when a user enters a page with many cards, the values defaulted to show will be the sum of 'This year' for all the cards.  If the user then wishes to view past year data, they can then apply their own filter of date.     

 

BlueRooster

To set the default to This Year, instead of All Time, edit the card in the analyzer. 

 

Make sure the DATE option is enabled, and then under the Date Range, click the drop-down and select Current -> This Year. Also make sure the "Hide Date on Card Details" is unchecked:

 

Now if you save the card, the default will be set to This Year but the user can click the date drop-down for the card and change the range if they wish.

 

Hope this helps,

ValiantSpur

 

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

khoang

Hi ValiantSpur,

 

Thanks for the screenshots.  The problem with this is that if the user decided to filter a date range outside the 'This Year' range, the data return is it doesn't exist.

 

The other issue I am running into is that you have to apply this logic to all the cards everytime it is created.  I am looking for is a having this 'filter' functionality in the page level as a default setting so that it will apply to other cards within a page.

 

Ken

BlueRooster

Ken,

 

Setting the initial date range to This Year and allowing it to be visible to the user let's the user change the range beyond the first year if they wish. We have several cards where we use This Year as the default and if the user wants to say look at the Previous Year instead, they just use the date range drop down to adjust it when viewing the card.

 

And to my knowledge there isn't a way to edit the date range for all cards on a page simultaneously.

 

Sincerely,

ValiantSpur

 

 

khoang

Hi ValiantSpur,

 

I've attached my sample mock data.  My fiscal does not follow a Jan-Dec calendar but rather is April - March.  My custom_date column is replacing the year in the original data column with the fiscal year.  Then my filter_date column is a fake date target to accommodate that if the current date is January through March, then I shift all the year in the custom date to +1.  The idea is that for all the fiscal year 2017 (April 1, 2017 to March 31, 2018), will be properly display when using the 'This Year' given that the actual year in current time is 2018 and not '2017'.  When the current date/time is outside April, then the filter date will revert back to the 'custom_date' values.

 

Now once I create the card to say 'This year' (which is 2018), I see my total values summed will be 22,200 (adding all the values in fiscal 2017).  But if I were to add a filter on the 'date' (original date of the data) of say April 1, 2016 to May 2016, my data return stating that it does not exist.  I have to first change the drop down to 'All Range' then add my filter which then allows the data to open up all the data ranges.  From a user navigation perspective, this can be quite confusing.  

 

 

DataMaven

If you click on the wrench at the page level, you will see a functionality to "Turn on Page Filters". 

 

Also - Make sure that your date fields are formatted as dates and not text.  If you want to isolate date parts, The "Date Operations" function in Magic ETL can help you make some nice easy filters.  

 

See if that does the trick!

khoang

Hi DataMaven,

 

Thanks for the note.  When you apply the page filter and adding a 'date' filter, the default drop down does not give you the ability to say 'This Year' as a quick option, it gives you options such as 'is greater than or equal to....'.  

With Page Level Filter, the problem that I run into is that the selection that was made say on my account does not store the 'selection' to say another user that I provide access to.  Example - if I apply filter ABC and store it.  Share the page to user B, when user B enter the page, the page filtered is not applied and user B has to apply filter ABC. 

What I was hoping for is a similar user experience to something like Google Data Studio dashboard where the date range control widget defaulted to last 30 days (in my case I want 'this year').  When a dashboard is shared to any user, the date range has already defaulted the data and the user does not need to apply any filter unless they want to.

 

Ken

 

Ken

BlueRooster

This might not be exactly what you're looking for, but I'm not sure how else to accomplish the functionality you're looking for. Ideally you would have an option for "This Fiscal Year" in the date options, but you could build a BeastMode filter to simulate that function:

CASE WHEN YEAR(CURRENT_DATE()) = (`fiscal`) + 1 THEN 'Current Fiscal Year'
     ELSE 
     CONCAT('Fiscal Year ',`fiscal`)
END

You could then add that as a quick filter and select the current fiscal year by default. But that would still allow the user to select a different year if desired.

DataMaven

This can also be done in Magic ETL, using the datepart.  The difference is that you would take the year and make it its own column.  If you can't assume that folks know which year is the current one, you could do mapping that has to be updated annually, though I would hope that wouldn't be necessary!  lol

 

You can also customize a field pretty precisely using combinations of date parts, dummy fields, concatenation, and formatting.  If you get creative, you can set up your ETL upstream so that you can filter by precisely the values you want to see downstream.