Tableau - Segmenting data into 3 sections

Question

I have a few months worth of retail data and am looking to do a general analysis on any potential lift in sales.

How can I group or segment the data so it's easier to few the target date ranges.

For example, if I have 3 months of data and want to group each month in their own section, how do I do so?

Thanks, Cafe

Answer 1

My suggestion would be to create a calculated field which does this grouping for you. I have followed this up with an example using an arbitrary dataset containing dates to mimic the scenario which OP mentioned in the comments.

+-----------+-------------+-------+
|   Date    | Hour of Day | Count |
+-----------+-------------+-------+
| 9/20/2016 |           6 |   100 |
| 9/21/2016 |           7 |    23 |
| 9/22/2016 |          15 |    58 |
| 9/23/2016 |          13 |    78 |
| 9/24/2016 |           3 |    32 |
| 9/25/2016 |          12 |    54 |
| 9/26/2016 |           0 |    32 |
| 9/27/2016 |          23 |    11 |
| 9/28/2016 |          11 |    10 |
+-----------+-------------+-------+

I am loading this dataset into Tableau. I create a calculated field called 'Date Grouping' with the following formula. The [Date] in this formula is the Date from my dataset. You can switch this out with whatever your date field is called.

IF DATEDIFF('day', DATEPARSE("MM-dd-yyyy",'09-22-2016'), [Date]) < 1 THEN 'Before 9/22' 
ELSE (IF DATEDIFF('day', DATEPARSE("MM-dd-yyyy",'09-25-2016'), [Date]) < 1 THEN '9/22 - 9/25'
ELSE 'After 9/25'
END)
END

This creates a new column as shown below (I copied this from the data source in Tableau)

+-----------+---------------+-------+-------------+-------------------+
|   Date    | Date Grouping | Count | Hour of Day | Number of Records |
+-----------+---------------+-------+-------------+-------------------+
| 9/20/2016 | Before 9/22   |   100 |           6 |                 1 |
| 9/21/2016 | Before 9/22   |    23 |           7 |                 1 |
| 9/22/2016 | Before 9/22   |    58 |          15 |                 1 |
| 9/23/2016 | 9/22 - 9/25   |    78 |          13 |                 1 |
| 9/24/2016 | 9/22 - 9/25   |    32 |           3 |                 1 |
| 9/25/2016 | 9/22 - 9/25   |    54 |          12 |                 1 |
| 9/26/2016 | After 9/25    |    32 |           0 |                 1 |
| 9/27/2016 | After 9/25    |    11 |          23 |                 1 |
| 9/28/2016 | After 9/25    |    10 |          11 |                 1 |
+-----------+---------------+-------+-------------+-------------------+

This calculated field can now be added to colour/size to create a grouping within the chart as shown below

Tight Bound on the Range

As you has mentioned your ranges in the comments, I have edited the calculated field query to create a tight bound instead of an open bound. The [Date] in this formula is the Date from my dataset. You can switch this out with whatever your date field is called.

IF (DATEDIFF('day', DATEPARSE("MM-dd-yyyy",'05-08-2016'), [Date]) > 0 AND DATEDIFF('day', DATEPARSE("MM-dd-yyyy",'06-13-2016'), [Date]) < 0) THEN '5/9 - 6/12' 
ELSE (IF (DATEDIFF('day', DATEPARSE("MM-dd-yyyy",'06-12-2016'), [Date]) > 0 AND DATEDIFF('day', DATEPARSE("MM-dd-yyyy",'07-18-2016'), [Date]) < 0) THEN '6/13 - 7/17' 
ELSE (IF (DATEDIFF('day', DATEPARSE("MM-dd-yyyy",'07-17-2016'), [Date]) > 0 AND DATEDIFF('day', DATEPARSE("MM-dd-yyyy",'08-22-2016'), [Date]) < 0) THEN '7/18 - 8/21' 
ELSE 'Out of Range' END) END) END

Input Table (dates have been modified to suit the date range mentioned)

+-----------+-------------+-------+
|   Date    | Hour of Day | Count |
+-----------+-------------+-------+
| 5/9/2016  |           6 |   100 |
| 5/10/2016 |           7 |    23 |
| 5/11/2016 |          15 |    58 |
| 6/12/2016 |          13 |    78 |
| 6/13/2016 |           3 |    32 |
| 6/14/2016 |          12 |    54 |
| 7/17/2016 |           0 |    32 |
| 7/18/2016 |          23 |    11 |
| 7/19/2016 |          11 |    10 |
| 8/21/2016 |          23 |    11 |
| 4/9/2016  |          15 |    58 |
| 8/22/2016 |          13 |    78 |
+-----------+-------------+-------+

After adding the calculated field 'Range'

Answer 2

You could use any date/datetime dimension in two ways:

• Truncated Date (blue one)
• Date Part (green one)

more info on how to set it up you will find here. If you will use Date Part on the month level all data is segmented into groups which consist only of entries from this particular month. This allows you to add another dimensions below month in order to go deeper with analysis.

If you would like to create rather custom date ranges (ex. 03.09 - 23.09) you could use Sets. Just drill down to day, select range you are interested in and create a new set. If you would like to compare few different sets, use calculated field to create a single dimension which will be grouping data based on information to which set single data entry belongs.

IF [Set 1] THEN 'Name of first set'
ELSEIF [Set 2] THEN 'Name of second set'
ELSE 'Other'
END