A Way To Create A Custom Time Aggregate Filter and Selector

ShaneenW_*50257
ShaneenW_*50257
in Modeling

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

ShaneenW_0-1642948476909.png

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

 

ShaneenW_1-1642948476919.png


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

ShaneenW_2-1642948476926.png

 

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.

ShaneenW_3-1642948476929.png

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.

ShaneenW_4-1642948476942.png

ShaneenW_5-1642948476951.png


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.

ShaneenW_6-1642948476958.png

ShaneenW_7-1642948476971.png

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.

ShaneenW_8-1642948476972.png

ShaneenW_9-1642948476973.png

ShaneenW_10-1642948476974.png

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

ShaneenW_11-1642948477065.png

 

ShaneenW_12-1642948477069.png

 

ShaneenW_13-1642948477072.png

 

Tagged:

Answers

  • rob_marshall
    rob_marshall

    @ShaneenW 

    Instead of a post, this should really be an article in the How To section so it doesn’t get lost in the shuffle.  Also, in order to be “compliant “ with best practices, I would take another look at your filter where you have three different conditions.


    Rob

     

  • ManjunathKN
    ManjunathKN

    Hi

     

    Very nice post.

     

    I want to know that how custom time settings is going to help for different time summaries ?

     

    Thanks,

    Manjunath

Categories