It was Kyle’s turn to set the challenge this week, and took inspiration from a work based scenario he had encountered. As a result 3 fictitious data sets were provided, and a viz was required to be built against each of them.

Building the Marketing Campaigns Gantt chart

All 3 vizzes are tied together by 2 parameters – the highlight date and number of days, so lets’ start with defining those

pHighlightDate

date parameter defaulted to 01 Dec 2023

and

pDays

integer parameter defaulted to 120

On a new sheet, show both of these parameters.

Using the Campaign data source, add Campaign to Rows and Start Date to Columns as a continuous exact date (green pill).

To define the width of each mark, we need

Duration

DATEDIFF(‘day’, [Start Date], [End Date])

Add this to the Size shelf, then add Category to the Colour shelf and adjust accordingly.

Add Start Date as a discrete exact date (blue pill) to Rows and position in front of the Campaign pill. This will order each row based on the Start Date ascending.

Hide the Start Date pill on Rows (uncheck Show Header). Add End Date as a continuous attribute to Tooltip, and amend the tooltip as required.

Add pHighlightDate to Detail, then right click on the Start Date axis and Add Reference Line. Add a dotted line for the entire table based on the pHighlightDate field.

Remove all row and column dividers; delete the axis title (right click > edit axis) and hide the Campaign column heading (right click > hide field labels for rows). Format the Campaign numbers so they are aligned right.

Update the title of the sheet, and include the details for the legend title within

Finally, we need to filter the rows shown based on whether the campaign was running within the window based on the number of days before/after the highlight date. We need

Window Start

DATE(DATEADD(‘day’, [pDays]*-1, [pHighlightDate]))

Window End

DATE(DATEADD(‘day’, [pDays], [pHighlightDate]))

and then

Campaigns to Include

[End Date] > [Window Start] AND [Start Date] < [Window End]

Add Campaigns to Include to the Filter shelf and set to True. Test the viz changes as the parameters are changed.

Building the Experiments Gantt Chart

This is built in very much the same way as the above using the Experiments data source instead. In this case, Start Time and Experiment ID will be on Rows, Start Time on Columns and Status on Colour,

A Duration field should be on Size, but the calculation needs to be slightly different to handle those records where there is a start but not an end. In this case, we assume the experiment is still ongoing, so set the end to ‘today’.

Duration

DATEDIFF(‘day’, [Start Time], IFNULL([End Time], TODAY()))

A Window Start and Window End field will need to added to this data source too, and then

Experiments to Include

IFNULL([End Time], TODAY()) > [Window Start] AND [Start Time] < [Window End]

should be added to the Filter shelf, and set to True – at this point all the experiments that didn’t have a start date either will disappear. Add a title and the reference line and format as before

Building the Emails bar chart

On a new sheet, using the CRM data source, add Date as a continuous exact date (green pill) to Columns and Sent to Rows. Change the mark type to bar chart. Click on the Size button, and change to be Fixed.

Add Email Type to Colour and adjust. Update the Tooltip.

Create fields Window Start and Window End as before, then create

Emails to Include

[Date] > [Window Start] AND [Date] < [Window End]

and add to the Filter shelf, set to True.

Add pHighlightDate to the Detail shelf, and add a reference line as before.

Remove all row/column dividers, gridlines & axis lines; delete the axis titles; add a sheet title including the legends.

The add all 3 sheets to a dashboard – I placed them all in their own vertical container, so I could then distribute contents evenly.

And that should be it! My published viz is here.

Happy vizzin’!

Donna