How To Create Customized "Time Frame" Radio-Button Filter

[Community_Admin]

This post will show how to create an easy to use Radiobutton filter (single selection type) with a list of predefined custom time frames. The created time frames can also be used for breaking by charts or pivots, providing a visual comparison between custom time frames.  

Example

Steps

• Create a time frame flag custom field for each desired time frame, give it the name of the time frame. you can use the following syntax for the above time frames:

Today: CASE WHEN daydiff(now(),DATE) = 0 THEN 1 ELSE 0 END

Last 2 days:  CASE WHEN daydiff(now(),DATE)< = 1 THEN 1 ELSE 0 END

Last 7 days:  CASE WHEN daydiff(now(),DATE) <= 6 THEN 1 ELSE 0 END

This month:  CASE WHEN monthdiff(now(),DATE) = 0 THEN 1 ELSE 0 END

Past month:  CASE WHEN monthdiff(now(),DATE) = 1 THEN 1 ELSE 0 END

*When using a Dim Dates table, the CASE WHEN syntax should be merged in the SELECT section

(i.e SELECT date,

CASE WHEN daydiff(now(),DATE) = 0 THEN 1 ELSE 0 END AS ‘Today’,

CASE WHEN daydiff(now(),DATE) <= 1 THEN 1 ELSE 0 END AS ‘Last 2 Days’

FROM…

UNION ALL)

2.   create a “Time Frame” table containing all time frames and the list of contained dates for each frame by adding a custom SQL table with the following syntax (for the above example):

SELECT  d1.Date AS date,'Today' AS [Time Frame Filter]

FROM Dates d1

WHERE d1.Today = 1

UNION ALL

SELECT  d2.date AS date,'Last 2 days' AS [Time Frame Filter]

FROM Dates d2

WHERE d2.[Last 2 days] = 1

UNION ALL

SELECT  d3.date AS date, 'Last 7 days' AS [Time Frame Filter]

FROM Dates d3

WHERE d3.[Last 7 days] = 1

UNION ALL

SELECT  d4.date AS dae, 'This month' AS [Time Frame Filter]

FROM Dates d4

WHERE d4.[This Month] = 1

UNION ALL

SELECT  d5.date AS date,'Past month' AS [Time Frame Filter]

FROM Dates d5

WHERE d5.[Past Month] = 1

3. Connect the time frame table to to the relevant table on “Date” and set the Date field in “Time Frame” table to “invisible”. The result should be similar to:

4. Build the cube, now time frames are available for Dashboards.
5. Create a widget, add the time frame field as filter on dashboard level and set it to “Radiobutton”

The created time frames can be also used as Columns in pivots, as categories in Pie Charts or as “break by” in charts, providing comparison between the time frames:

*Attached below are Ecdata and .Dash file of the above example

Important Note

In order to prevent data duplication by the "time frame" options, the created "time frame" filters should only be used as a visible “break by” or “Radiobutton” filter and not as a “Multi-selection” filter. 

Download:

Time Frame Example.ecdata

TimeFrameExample.dash