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):
Then, another column to calculate the actual range based on the buckets (these could be combined):
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):
When we select the “Time Bucket” column, we can use the “Sort By Column” option to choose “TimeBucketOrder”:
Now we can create visualizations like these:
Results
If we filter by “Past”, will take all months before the current one:
Current and next quarter:
And if we select America’s region, we highlight the values for that region on the selected range of dates:
Related Posts
- “Grouping in Ranges/Bands” for ways to simplify a bit this scenario.
- “Use Ranges/Bands for measuring Frequencies and visualizing Empirical Distributions”.