A Way To Create A Custom Time Aggregate Filter and Selector

ShaneenW

Custom Time Aggregate Filter and Selector by @JohnB & @ShaneenW 

We present our approach to creating a custom time aggregate filter and selector. We look forward to learning a more efficient approach to creating a time aggregate filter/selector or tips/tricks to shorten the approach below.

 

1. Create a flat (non-hierarchical) list: Month, Quarter, Year

2. Create or use an existing Data Module containing line items to be filtered by custom time filter.

The line items of interest should have the timescale: Month, Quarter, and Years. In this case, we have the same data for each line item with different timescales.
For naming conventions, numbers represent the data and letters refers to timescale (i.e. Same data exists in Data 1 by timescales A (month), B (quarter), C (year)).

Line Items	Formula	Time Scale
Data 1		Month
Data 1A	MONTHVALUE(Data 1)	Month
Data 1B	QUARTERVALUE(Data 1)	Quarter
Data 1C	YEARVALUE(Data 1)	Year
Data 2		Month
Data 2A	MONTHVALUE(Data 2)	Month
Data 2B	QUARTERVALUE(Data 2)	Quarter
Data 2C	YEARVALUE(Data 2)	Year

 

3. Create a List Item Subset from Data Module containing line items of interest:

 

4. Create first mapping module with dimensions by line-item subset (created in step 3) and flat list (from step 1) in a new module. Create one line item, Mapping, with format as Boolean. We have the line item as page, flat list as column and line-item subset as rows.

5. Create second mapping module with dimensions: time, line-item subset (created in step 3) and flat list (from step 1) in a new module. Create three line-items: Filter 1, Filter 2, and Filter 3 with timescales, month, quarter, and year. The format is number for all line items. Since we are filtering between three filters, we utilize numerical filtering approach.

The general formula for line items is if flat list item = Month (or other time aggregate) and Mapping line item (from step 6) is true then 10 (or any ##) else 0.

6. In the Data Module, apply filter using the mapping module (from step 3) to line item and apply another filter using mapping module from step 5) to time.

As a result, the time aggregate selector will show only months for month selection, quarters for quarter selection, and years for year selection. It removes empty timescales.

In App view, the chart updates accordingly using the time aggregate filter/selector. The selector is in the bottom left corner.