This week, another guest poster, Jami Delagrange, provided the #WOW2020 challenge. And I’m not gonna lie, I found this pretty tough.

The challenge suggests you can build the data model yourself, and I thought about doing this, but then figured I’d solve the visualisation challenge itself before looking back at the data model… and I’m glad I did, because it took some time to get through the challenge (and I haven’t attempted to build the model – I’ll wait for Jami’s solution for that).

So what were the areas I struggled with….

Initially it was the mark type, and how to get the ‘bars on bars’, in such a way that the fat weekly bar was sat in the middle of the thin daily bar. I tried a variety of things and spent a lot of time, with no success, before I gave up and had to look at Rosario Guana‘s solution. The answer Gantt bars. But I’d already attempted them, and hadn’t made much progress, and even after trying again having seen that Gantt;s were needed, I was still puzzled. So I had to have another look, and realised that my problem was that I was trying to plot a measure to make a vertical axes (ie put a measure on the rows), and that wasn’t needed.

So now armed with those 2 bits of information – I needed Gantt bars and set the measures for the dual axis on the Columns – I could start figuring out all the pieces I needed.

Within this blog, I’ll focus on

- Calcs required for plotting the Weekly Sales data
- Calcs required for plotting the Daily Sales data
- Building the Gantt chart
- Comparison filter
- Event Group filter

Weekly Sales data

There isn’t a single ‘launch date’, across all the events, but the charts all start at the ‘same point’, so we can’t be plotting the date on the axis. Instead we need to calculate the week no relative to the launch date per event, using

**Week of Launch Date**

DATE(DATETRUNC(‘week’,[Launch Date],’Monday’))

(the requirements state the week should start on a Monday)

**Week of Actual Date**

DATE(DATETRUNC(‘week’,[Actual Date],’Monday’))

**Week No From Launch**

DATEDIFF(‘week’, [Week of Launch Date], [Week of Actual Date]) + 1

This returns an integer starting from 1, and is essentially counting the weeks from launch up until the week of the Event itself.

For the weekly data, we also need the ticket sales for the week, the cumulative weekly ticket sales (both displayed on the tooltip), the total sales and the % of sales per week (used to colour the mark, and displayed on the tooltip).

**Ticket Sales**

ZN([Sold Amount])

by wrapping this in **ZN** the field will return 0 in the event there are no tickets sales in a week. This means a mark will exist for every week.

For the cumulative ticket sales, I just used a Running Sum **quick table calculation** on **Ticket Sales**, so didn’t create a dedicated field, but if I had if would have been RUNNING_SUM(SUM([Ticket Sales])).

**Total Sales Per Event**

TOTAL(SUM([Sold Amount]))

**% Total Sales**

RUNNING_SUM(SUM([Ticket Sales])) / ([Total Sales Per Event])

This is formatted to a percentage with 1 dp.

If we put this all out into a table as follows, then you can see how the data all holds together

**Event Description (Dim Event)**&**Week No From Launch**(blue discrete pill) on**Rows****Measure Names**on**Columns**/**Measure Values**on**Text**, filtered to measures :**Ticket Sales**, Ticket Sales (running sum table calc),**Total Sales Per Event**, &**% Total Sales**.. All table calcs are set to compute using**Week No from Launch**.

Daily Sales Data

For each week of sales, we need to plot 7 marks for each day of sales. Again we can’t plot using the actual date of sale. Instead we need to ‘index’ each day in the week – ie number each day from 1 – 7.

The data set already has a **Day of Week **field associated to **Actual Date**, but this returns 1 for a Sunday and 7 for a Saturday, but we need 1 to be associated to a Monday, so we just need to translate this field

**Day No of Week**

IF [Day of Week] = 1 THEN 7

ELSE [Day of Week] – 1

END

The days plotted are also coloured based on which ‘type of day’ they are

**Type of Day**

IF [Actual Date] = [Launch Date] THEN ‘Launch Date’

ELSEIF [Actual Date] = [Event Date (Dim Event)] THEN ‘Event Date’

ELSE ‘Regular’

End

Once again let’s plot the info we need into a table, so you can see what’s going on:

Put **Event Description (Dim Date), Actual Date (exact date, discrete blue pill), Week No from Launch, Day Of Week Abbrev, Day No of Week, Type of Day** on **Rows** and **Ticket Sales** on **Text.**

So these are the basic building blocks we need.

Building the Gantt Chart

For the ‘daily’ view, we need

**Event Description (DimEvent)**on**Rows****Week No from Launch**(discrete, blue bill) on**Columns****Day No of Week**(Continuous, dimension, green pill) on**Columns**

By just placing these 3 pills in these locations, the mark type is ‘automatic’ but has immediately selected the ‘gantt bar’ type. Change it to be specific.

You can now add **Type of Day** to **Colour** and format the marks appropriately (change the colour, set a border around each mark).

For the ‘weekly’ view, we just need a way of creating a mark that ‘spans’ the 7 days, as we’ve already ‘segmented’ the display by **Week No From Launch**. I typed in **MIN(0)** to **Columns** for this

I then ‘typed’ in Min(1) on the Marks shelf and added it to the **Size** shelf

Editing the **MIN(0) **axis to fix it from 0 to 1, fills the space, and then adjust the **Size** of this mark to make it narrower.

If you now make this ‘dual axis’, you’ve got the view you need

And now it’s just a case of formatting the colours, the Tooltips for each mark, hiding axis headers etc.

Comparison Filter

This filter changes the order in which the rows are displayed.

This is driven by a **Parameter** which contains the values ‘Event Year’ and ‘YoY Event’ and a calculated field

**Display**

IF [Comparison] = ‘Event Year’ THEN LEFT([Event Description (DimEvent)],4)

ELSE RIGHT([Event Description (DimEvent)],2)

END

Add the **Display** to the **Rows** in front of the **Event Description (DimEvent)** field and hide it.

Event Group Filter

Another parameter **Event Group** is created which I’ve set to store integers but display as a string

I also then created

**Event Group Filter**

([Event Group] = 1 AND QUARTER([Event Date (Dim Event)])<=2) OR ([Event Group] =2 AND QUARTER([Event Date (Dim Event)])>2)

OR [Event Group]=0

which is added to the **Filter** shelf and set to **true**.

And hopefully that should be all you need to get this challenge sorted. My published viz is here, and if you download, I also have other sheets which try to breakdown some of the information.

Happy vizzin’! Stay safe!

*Donna*