Academy

How to: create a time bucket dropdown

6th July 2023

Wondered how to create a time bucket dropdown in icCube?

Here’s a step-by-step guide.

To go further, we’ll also show how a chart can change its axis depending on the time bucket. For example, a column chart that shows data by day if the time bucket is “Last week” or “Last month”, and data by month if “Last year”.

Foreword
Time buckets definition
Time bucket dimension
Time bucket function
Time bucket filter
Applying the time bucket filter
Axis dependent on the selected time bucket

0. Foreword

Before we start, remember to always be careful when working with dates. Make sure the calculations you are choosing are the ones expected. For example, if you’re looking to get the previous year week, what do you mean?

Same week number as previous year (e.g. Week 23 2023 vs. Week 23 2022)
Week of current day vs. Week of same day of previous year (careful, e.g. June 4th is on Week 23 in 2023 and in 2022 it’s on Week 22)

The function to be used will depend on this choice, make sure to carefully check the time intelligence functions’ definitions.

1. Time buckets definition

On your schema, create a simple table with your buckets

2. Time bucket dimension

Create the time buckets dimension (on the Wizard, click on “Create with Dimension/Facts Links Not Mapped”)

3. Time bucket function

In the schema’s script, insert the function below. Basically, this function takes “bucket” as an argument, which will be the event of your dropdown filter in your dashboard. If you select Last week then this function will create the list of dates from Last week until today, etc.

CREATE FUNCTION BucketDates(bucket) as  
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last week],    dtMinusWeeks(dtAsOfToday([Time].[Time].[Day]),1):dtAsOfToday([Time].[Time].[Day]),
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last month],   dtMinusMonths(dtAsOfToday([Time].[Time].[Day]),1):dtAsOfToday([Time].[Time].[Day]),
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last year],    dtMinusYears(dtAsOfToday([Time].[Time].[Day]),1):dtAsOfToday([Time].[Time].[Day]),
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last 3 years], dtMinusYears(dtAsOfToday([Time].[Time].[Day]),3):dtAsOfToday([Time].[Time].[Day]),
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last 5 years], dtMinusYears(dtAsOfToday([Time].[Time].[Day]),5):dtAsOfToday([Time].[Time].[Day]),
                  [Time].[Time].[Day]
                  )  
                  )
                  )
                  )
                  )

Here’s the list of time intelligence functions. Note that you could also use the Lag() function.

4. Time bucket filter

In your dashboard, create your dropdown filter. You need to write the MDX as not mapped dimensions do not appear on the query builder tree.

SELECT
   [Time Buckets].[Time Buckets].[Time Buckets].Members ON "MdxBuilderFilterItems"
FROM [Sales]
CELL PROPERTIES CELL_ORDINAL

Name the dropdown bucket in Interaction > Selection > Publish Selection.

5. Applying the time bucket filter

Create a table widget such as:

SELECT
   [Measures].[Amount] on 0
   BucketDates(@{bucket!∅}) ON 1
FROM [Sales]

And voilà!

6. Axis dependent on the selected time bucket

To go further, imagine you need a column chart that shows data by day if the time bucket is “Last week” or “Last month”, and data by month otherwise. Create a Column Chart with the following query statement:

WITH
  SET myDateAxis as iif(@{bucket!∅} is [Time Buckets].[Time Buckets].[Time Buckets].[Last week]
                       or @{bucket!∅} is [Time Buckets].[Time Buckets].[Last month],
                        [Time].[Time].[Day],
                        [Time].[Time].[Month]
                    )

SELECT
   [Measures].[Amount] on 0
   myDateAxis on 1
   
FROM [Sales]
FILTERBY BucketDates(@{bucket!∅})

Here we’re saying that we take the day level if the time bucket (the dropdown filter event) is “Last week” or “Last month”, or else we take the months level. Don’t forget the FILTERBY with the BucketDates() function.

With this, you now select the buckets on your dropdown:

You have days in the axis if you select “Last week”:

and months if you select “Last year”:

Schema & dashboard files

Find the files here.

Schema – drag & drop it into the icCube Builder
Data files – upload it on Admin > Docs > File System Root.
Dashboard – drag & drop it into the icCube Dashboard Console > Dashboard Editor > Import Dashboard

By Nathalie Leroy Tapia Heredia

search articles