Skip to main content

Implementation Method for Filter Default Display of Current Fiscal Quarter

Scenario

Different industries may divide fiscal years and fiscal quarters according to different standards. Financial reports need to be filtered and display current fiscal quarter data by default. However, fiscal years and fiscal quarters are not like standard quarters and months that can use time macros to directly set default filter items. This article will introduce how to make filters display and filter current fiscal year and fiscal quarter by default. In the following case, June is the start of a new fiscal year and fiscal quarter, and May 2022 is the 4th fiscal quarter of fiscal year 2021.

image.png

Step One: Data Preparation

1. In the dataset overview page or ETL, create new calculated fields:

"Year", numerical type, use the year() function to get the natural year of each row's date;

"Month", numerical type, use the month() function to get the natural month of each row's date.

2. Create new calculated fields (or grouping fields) to get the fiscal year and fiscal quarter of the data date, or create fields to concatenate fiscal year and fiscal quarter according to needs. Formula reference the figure below.

image.png

Step Two: Scenario Implementation

Scenario One

The latest date in the dataset is less than or equal to the current month, default display current fiscal quarter or latest fiscal quarter.
On the page, create a new selection filter, select the required fields (fiscal year/fiscal quarter/fiscal year fiscal quarter), default value select "First in the list"; click the arrow on the right side of "Options" to select "Advanced Sorting", select the date field, set "Descending" and save. If filtering the concatenated field "Fiscal Year Fiscal Quarter", you can also directly sort the current field in descending order.

image.png

Scenario Two:

The latest date in the dataset is greater than the current month (such as fiscal quarter budget and target tables), filter items don't need to display future fiscal quarters.

1. In the dataset or ETL, create a new calculated field "Is Future Date", boolean type, save.

image.png

2. Page creates a new selection filter, refer to scenario one to set "First in the list" and "Descending" for date in advanced sorting, and additionally click "Filter" after options, filter "Is Future Date" as "true" and save.

image.png

Scenario Three:

The latest date in the dataset is greater than the current month (such as fiscal quarter budget and target tables), filter items default display current fiscal quarter, dropdown menu needs to display all fiscal quarters.

1. Create 2 new calculated fields "Current Fiscal Quarter" and "Current Fiscal Year".

image.png

2. If the filter uses "Fiscal Year Fiscal Quarter" concatenated display, create a new calculated field "Fiscal Year Fiscal Quarter Order", compare the data date with the current date, the order of the fiscal quarter where the current month is located is 1, and the rest are arranged in descending order by fiscal year fiscal quarter;

image.png

If the page uses 2 filters to filter fiscal year and fiscal quarter separately, you need to create 2 calculated fields to sort fiscal year and fiscal quarter respectively.

image.png

3. On the page, create a new selection filter, select the required fields (fiscal year/fiscal quarter/fiscal year fiscal quarter), default value select "First in the list"; click the arrow on the right side of "Options" to select "Advanced Sorting", add the corresponding sorting field for the filter field created in the previous step, default "Ascending" and save.

image.png

The implementation effect is shown in the figure below. The default filter item automatically changes to the next month, and June 2022 is the 1st fiscal quarter of fiscal year 2022.

image.png