BI as Business Insight

Proving that what you do has real impact.

Ranges as Filters for Dates

Scenario

We may have data across months, and we’d like to, at times, focus on the next 3 months, the following quarter, or the first 6 months… or similar, i.e. how many people between 20-30 years old, or 30-40, etc. How do we define these ranges, and how can we visualize them?

HowTo

Let’s say we have a dataset measuring costs for projects across two years. We’d like to have ranges for:

  • All months before today.
  • This current month.
  • The next 3 months.
  • The following 3 months.
  • The next year.

These calculations will be needed in the “Dates” table. First we need a column to calculate the buckets (in this case, the number of months from today):

image

Then, another column to calculate the actual range based on the buckets (these could be combined):

image

Finally, a column that we’ll use to order the ranges when we display them (on a logical order that makes sense, and not alphabetically):

image

When we select the “Time Bucket” column, we can use the “Sort By Column” option to choose “TimeBucketOrder”:

image

Now we can create visualizations like these:

image

Results

If we filter by “Past”, will take all months before the current one:

image

Current and next quarter:

image

And if we select America’s region, we highlight the values for that region on the selected range of dates:

image

Related Posts