Configure a Filter to Show the Current Fiscal Quarter by Default
Scenario
Different industries may define fiscal years and fiscal quarters by different standards. Financial reports need to filter and display current fiscal quarter data by default. However, unlike standard quarters or months, fiscal years and fiscal quarters cannot use time macros directly to set default filter options. This document describes how to make a filter display and filter the 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 Q4 of fiscal year 2021.
Step 1: Data Preparation
1. Create calculated fields on the dataset overview page or in ETL:
Year: numeric type. Use the year() function to obtain the natural year of each row's data date.
Month: numeric type. Use the month() function to obtain the natural month of each row's data date.
2. Create calculated fields or grouped fields to obtain the fiscal year and fiscal quarter of the data date. You can also create a field that concatenates fiscal year and fiscal quarter as needed. See the figure below for the formula.
Step 2: Scenario Implementation
Scenario 1
The latest date in the dataset is less than or equal to the current month, and the filter should display the current fiscal quarter or the latest fiscal quarter by default.
Create a selection filter on the page, select the required field, Fiscal Year, Fiscal Quarter, or Fiscal Year and Quarter, and set the default value to First in List. Click the arrow on the right side of Optional Values, select Advanced Sorting, select the date field, set Descending, and save. If you filter the concatenated field Fiscal Year and Quarter, you can also sort the current field directly in descending order.
Scenario 2
The latest date in the dataset is greater than the current month, such as a fiscal quarter budget or target table, and filter options should not display future fiscal quarters.
1. Create a Boolean calculated field named Is Future Date in the dataset or ETL, and save.
2. Create a selection filter on the page. Refer to Scenario 1 to set First in List and sort the date in Advanced Sorting by Descending. In addition, click Filter after Optional Values, filter Is Future Date by true, and save.
Scenario 3
The latest date in the dataset is greater than the current month, such as a fiscal quarter budget or target table. The filter option should display the current fiscal quarter by default, while the dropdown menu should display all fiscal quarters.
1. Create two calculated fields: Current Fiscal Quarter and Current Fiscal Year.
2. If the filter uses the concatenated Fiscal Year and Quarter field for display, create a calculated field named Fiscal Year and Quarter Order. Compare the data date with the current date. The fiscal quarter order of the current month is 1, and the rest are sorted in descending order by fiscal year and fiscal quarter.
If the page uses two filters to filter fiscal year and fiscal quarter separately, create two calculated fields to sort fiscal year and fiscal quarter separately.
3. Create a selection filter on the page, select the required field, Fiscal Year, Fiscal Quarter, or Fiscal Year and Quarter, and set the default value to First in List. Click the arrow on the right side of Optional Values, select Advanced Sorting, add the sorting field corresponding to the filter field created in the previous step, keep the default Ascending, and save.
The effect is shown below. In the next month, the default filter option changes automatically. June 2022 is Q1 of fiscal year 2022.
