Author: Hanwen Chen: Certified Master Anaplanner and Professional Services Sr. Manager at Anaplan.
Dynamic Time Filters and Dynamic Line-Item Filters are common features in Anaplan models that provide users with greater flexibility when viewing reports.
Dynamic Time Filter
A Dynamic Time Filter allows users to select the time granularity they want to see in a report, such as month, quarter, or year.
For example, users can select whether they want to view the report by month, quarter, or year, and the report will dynamically filter the appropriate periods.
Dynamic Line-Item Filter
When a report contains many line items, users may want to focus on a specific line item. A Dynamic Line-Item Filter can be used to provide this flexibility.
For example, if a report contains multiple line items and a user only wants to see the Adj line item, they can select the Show Adj Only option to dynamically filter the report.
This article provides a step-by-step guide to setting up both Dynamic Time Filters and Dynamic Line-Item Filters, including a Polaris-compatible approach for Dynamic Time Filters.
Dynamic Time Filter setup
In Classic, formulas can combine line items with different timescales. In Polaris, the timescale of a line item referenced in a formula must be compatible with the target line item’s timescale. Therefore, the setup differs slightly between Classic and Polaris.
Dynamic Time Filter setup in Classic
Step 1: Set up the Period Selection Module
Create the user period selection module “U02: Select Show Period by Year” with the following line items.
Step 2: Set up the Filter Module
Set up the filter module “U03: Combo Selected Period by Year” with the following line items. The line item “Filter: Combo Selected Period by Year?” is the final time filter that can be applied to the report grid.
Line Item | Format | Formula | Summary | Time Scale |
|---|
Selected Show Month? | Boolean | YEARVALUE('U02: Select Show Period by Year'.Show Month?)
| None | Month |
Selected Show Quarter? | Boolean | YEARVALUE('U02: Select Show Period by Year'.Show Quarter?)
| None | Quarter |
Selected Show Year? | Boolean | YEARVALUE('U02: Select Show Period by Year'.Show Year?)
| None | Year |
Filter: Combo Selected Period by Year? | Boolean | Selected Show Month? OR Selected Show Quarter? OR Selected Show Year?
| Formula | Month |
Please note that Selected Show Quarter? and Selected Show Year? use timescales (Quarter and Year) that differ from the target line item, Filter: Combo Selected Period by Year?, which uses the Month timescale. The formula in the target line item works because Classic allows line items with different timescales to be combined within the same formula.
Dynamic Time Filter setup in Polaris
In Polaris, the timescale of a line item referenced in a formula must be compatible with the timescale of the target line item.
As a result, the standard Classic setup above needs to be adjusted for Polaris. In this particular use case, the solution is to bring all the time-based logic to the Month timescale.
The setup below works in both Polaris and Classic, so it can be used as a common approach.
Step 1: Set up the Period Selection Module
Use the same period selection module “U02: Select Show Period by Year” described in the Classic setup above.
Step 2: Set up the "Month Setting" Module
Set up a Month Setting module “T01: Month Setting-Model Calendar” with the following line items. The Month Setting module brings the quarter and year to the Month timescale, allowing them to match the timescale of the target line item.
Line Item | Format | Formula | Summary | Time Scale |
|---|
Month Count | Number | 1
| Sum | Month |
Month? | Boolean | TRUE
| None | Month |
Quarter? | Boolean | Month Count = 3
| Formula | Month |
Year? | Boolean | Month Count = 12
| Formula | Month |
Please note that Month Count has a Sum summary method. It accumulates the number of months within each quarter and year. The aggregated value is then used to set up the Boolean line items Quarter? and Year? at the Month timescale.
Step 3: Set up the Filter Module
Set up the filter module “U03: Combo Selected Period by Year” with the following line items. The line item “Filter: Combo Selected Period by Year?” is the final time filter that can be applied to the report grid.
Line Item | Format | Formula | Summary | Time Scale |
|---|
Selected Show Month? | Boolean | YEARVALUE('U02: Select Show Period by Year'.Show Month?)
| None | Month |
Selected Show Quarter? | Boolean | YEARVALUE('U02: Select Show Period by Year'.Show Quarter?)
| None, Time:Any | Month |
Selected Show Year? | Boolean | YEARVALUE('U02: Select Show Period by Year'.Show Year?)
| None, Time:Any | Month |
Filter: Combo Selected Period by Year? | Boolean | Selected Show Month? AND 'T01: Month Setting-Model Calendar'.Month? OR Selected Show Quarter? AND 'T01: Month Setting-Model Calendar'.Quarter? OR Selected Show Year? AND 'T01: Month Setting-Model Calendar'.Year?
| Formula | Month |
Please note that Selected Show Quarter? and Selected Show Year? both use the Month timescale, which matches the timescale of the target line item, Filter: Combo Selected Period by Year?. By aligning all line items to the same timescale, this approach avoids mixing different timescales within the formula and therefore works in both Polaris and Classic.
Dynamic Line-Item Filter Setup
In the example below, the report contains four line items: Adj, Avg Cost, Avg Cost w / Inflation and Avg Cost w/ Productivity.
Users want the flexibility to show only Adj or all four line items. A Dynamic Line-Item Filter can be used to provide this flexibility.
Below are the steps to set it up.
Step 1: Create a Line-Item Subset
Create a Line-Item Subset (LIS) for the module containing these four line items.
In this example, the module is CAL01 Historical Average Cost.
The Line-Item Subset is named LIS: CAL01 Historical Average Cost.
Step 2: Set Up the Property Module for the LIS
Create a property module, PROP24: LIS: CAL01 Historical Average Cost, for the LIS with the line item “Item”.
The line item “Item” is used in the formula below to create the filter.
Step 3: Set up the User Selection Module
Create a user selection module “U01: User Single Item Selection” that allows users to select whether they want to show only the Adj line item or all line items.
Step 4: Define a Global Variable for Adj
Set up a line item in the Global Setting Module “SYS03 Global Setting” to store the LIS item corresponding to Adj. This provides a centralized reference for the formula that creates the filter.
Step 5: Set Up Filter Module
Create a filter module “U04: User Selected Item by LIS: CAL01 Historical Average Cost” dimensioned by Users and the LIS.
The final Boolean line item, “Filter: Show Adj?”, can be applied as a filter to the report grid.
The formula for the line item is:
(NOT 'U01: User Single Item Selection'.Show Adj Only? OR 'U01: User Single Item Selection'.Show Adj Only? AND 'PROP24: LIS: CAL01 Historical Average Cost'.Item = 'SYS03 Global Setting'.'Adj-LIS: CAL01 Historical Average Cost')
Summary
A Dynamic Time Filter allows users to control the time periods displayed in a report, such as month, quarter, or year. A Dynamic Line-Item Filter allows users to control which line items are displayed, such as showing only the Adj line item or all available line items.
When implementing Dynamic Time Filters, it is important to consider the differences between Classic and Polaris. The approach described in this article avoids mixing incompatible timescales within the formula and provides a common setup that can be used in both Classic and Polaris.
I hope this article provides a clear and simple guide to setting up Dynamic Time Filters and Dynamic Line-Item Filters in Anaplan models.
Questions? Leave a comment!
……………
Other articles by Hanwen:
