Add 'Financial Month' Pre-defined Date Filter

Bobby Lee shared this idea 6 years ago
Idea Logged

Our financial year starts on December 22nd. This makes the first financial month 12/22 - 1/21. Can we have a configurable predefined date filter for financial months?

Replies (1)

photo
1

Hi Bobby,

I've logged this Idea as an Enhancement Request and will keep this post updated with further information on if and when we would implement such a feature.

In the meantime, there are a few ways that we can work around this issue.

The first is configuring a 'Grouped Data' field at the View level. Using this method, you can create a group for each fiscal month. While this method isn't dynamic, it takes relatively little time to set up. To do so:

Edit the View for the report you'd like to display fiscal months for. Under the 'Prepare' tab click the '+' button towards the bottom right of the left-hand pane. Choose 'Group Data'. Now we'll name this something meaningful, I'll use 'Fiscal Months'. For the 'Original Field' I'll choose my date field I'll be filtering on:


5d250c9d5e422328afce09b129fa53fc

Clicking 'Continue' will allow you to configure these groups. The first group, I'll label '2017 Fiscal Month 1'. I'll manually set these values from 12/22/2016 - 1/21/2017:

f7db1328fe20bd0687ce0be2f9342978

Clicking 'Save' stores this as a group. Now you can click '+ Add Group' to continue adding groups. Do this for each fiscal month, and save your View.

Now, let's review this at a report level. I've created a simple report with my date field in the Columns. I add my 'Fiscal Months' field to the Filters and choose 'In List' as my operand. As you can see, this allows me now to choose which Fiscal Months I'd like to display:

aa897af32aa01247f1693cb5ac86840f

Note that this list will show any values individually if they don't fall into a configured Group.

Another method that may be more dynamic is to configure a linked filter that takes input based on the start of a fiscal month and shows data for + x days. In example, if your fiscal months are 30 days even you can configure a filter that inputs a start or end of a fiscal month and shows data for + or - 30 days accordingly.

To do this, add your date field into the filter box and change this to 'Equal to'. Rename this field for future reference, i.e. 'Start Date'. Now add two instances of this same field to the filter box and click 'Advanced Settings'.

089eeaa9fee7bd52cfbb4c6f35ca99f9


From here you will want to change the first operand from 'And' to 'Or' to make this work correctly. The two date filters you added should both be set to 'Link to Filter':

6bf729c992b5d4b89fe7d7336b29786a

Click 'Define Link' for the first filter. Set this 'Equal to' and link to 'Start Date'. Make sure your 'Offset' is set to 0.

7e6828ecee7ea3c2503786274138c3bc


Now, define your second link. This is where we'll define our range. In my example I've used 'Less than or equal to Start Date + 30 Days'. Essentially giving me anything 30 days or less than Start Date.

8c6d72a3bfcf1192db0bb9611438c6ae

Coupled with my 'And Greater than or equal to', I get my 30 day range from the user input start date.

2df6bc24f12fb356ad6fa9878bc50500

While this second method is a bit more dynamic, it does depend on user input.

In the meantime, I'll keep this post updated with further information regarding this request and whether it's chosen for development.

Thanks,

Ryan

Leave a Comment
 
Attach a file