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)
- 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
- 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:
- Build the cube, now time frames are available for Dashboards.
- 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:
