Skip to main content

How to Analyze Year over Year Performance at the Weekday level

When I worked in industry, everyday I reported sales performance at the day level and compare its results against the same weekday the previous year.

“If we had a bad YoY comparison, did we have a big promotion the same day last year? Or if not, which categories did better or worse?”

To add to the complexity, the business had a regular promotional week where new pricing would update every Friday. So comparing Jan 6, 2017 (a Friday with new prices and promotional tactics) against Jan 6, 2016 (a Wednesday) was not helpful!

Its an excellent way to look at the business tactically, and get some actionable insights quickly. And luckily it’s really easy to do in Tableau!

Step 1: Find the same weekday last year

 

Step 2: Add the same datasource again

Here, I’ve added Superstore again through the other menu since it was giving me grief through the “Saved data sources” pane. This way, I can just simply navigate to the dataset sitting under “My Tableau Repository”

 

Step 3: Configure the blend through the Edit Relationships

Here we specify that we want the “Day/Month/Year” of Order Date and Order Date in the Previous Year to match, as well as the product subcategory. This is how we’ll be able to see which subcategories had new or declines in sales between the years.

 

 

Step 4: Validate your data post Edit Relationships

Put [Order Date] and [Order Date LY] from the same data source (the first one we pulled in) on rows and [Sales] from the same datasource on Text.

Then put [Sales] from your secondary datasource on the marks pane to create the following table.

In the view below, I’ve already renamed it to be [Sales LY] in the secondary datasource. But remember that its only because I’ve got my edit relationships set up and I’m using this as a secondary datasource that it will render sales this way. Otherwise, it will not retrieve sales from last year,

 

Want to check to see if its pulling in the right value? Just scroll up [Order Date] and check if its referencing the right sales amount. Here – the $732 in sales match!

 

Step 5: Create your Year Over Year Calculation

 

Then see what it looks like against time..

 

I find this view (while computationally correct) too messy and variable to garner any insight.

I find it much more useful when I filter the view to the last 10 days. Even better when I conditionally color it against if its done better or worse than last year!

 

 

Step 6: Build a dual axis bar chart  with [Sales] on 1 axis and [Sales LY] on the other.

 

Final Thoughts

In the last view, I’ve already got it filtering by a particular day, but I can easily attribute which categories I made gains in, and which ones I wasn’t able to produce better results compared to last year. Maybe the promotional tactic wasn’t as good, maybe there were extenuating factors.  But its great to know isn’t it?

 

Hope this will be helpful for you!

 

 

 

 

 

 

Viz Club – BBQs and Drinking Fountains in Melbourne

Viz Club is Back!

We revived Viz Club after interest on Twitter (and since Eva Murray and Carsten Weidmann were in town!). I was running late that day and unbeknownst to me, everyone else at Viz Club that day.. had no idea how it worked.

For the London group, I think I’ve figured out a formula:

1. #ThrowbackThursday on Spotify.

Something about Michael Jackson/ABBA/everything 80’s just seems to have the right head bop/shoulder shimmy for a weeknight viz session.

2. The Viz Driver

In this case it was Lorna because Pablo volunteered her first. Sneaky tricks from Coach Kriebel at play!

3. Backseat drivers

i.e. the rest of us. Sarah Bartlett, Amanda Patist, Ian Baldwin, Charlie Hutcheson, Naledi Hollbruegge, Tommy Lees, Eva Murray  and Carsten Weidmann. But to be fair, I’m 80% certain Ian was there because I highjacked his computer since it was already connected to the speaker – so he couldn’t go home.

4. Beer and pizzas.

 

Its a rough gig being the driver- but I think its a nice payoff, getting a free viz to post to your profile after. Check out this piece of magic!

The Data

We explored Melbourne’s Public Assets – namely shape files on Melbourne’s sidewalks, water fountains, barbecues and its City Circle tram. We connected through Exasol, already preloaded by Eva and Carsten for us 😀

Looking at this data was a nice distraction from the snow warning we got on the news.  Eva kindly found the data and even pushed the shape files through Alteryx to create the .tdes!

Exploring the data

It turned out that all the datasets were maps. Which is why its not surprising one of our first views came out like this. We call him “Map Man”.

 

 

We eventually started understanding which fields go where on the canvas. Here’s us mesmerized by sidewalks dataset.

There’s something so soothing about seeing map data plotted perfectly against a background map.

 

 

Tableau Techniques

One of the upsides of Viz Club (other than a sensible weeknight party) is learning Tableau!

Using Mapbox in Tableau

I use mapbox pretty often, but quite a few people didn’t know how exactly to hook it up to Tableau. After showing that all you needed was a free license key to plop into the maps section– voila! “Ooos” and “ahhhs” all arround. If you’re interested, the map is the free “Pirates” theme – who knew right?

 

Flat Icon and The Noun Project for Free Icons

If you’re in need of a free icons – look up Flat Icon and The Noun Project. Great selection and usability! Thank you Pablo and Lorna for those!

 

How to color an Image Shape

This tip made me go “Hold up – rewind – what did you just do??”.

If you’re using images in your shape files, you can color them by creating a new dimension (“Tram”) then putting it on color!

 

All this Tableau-ing is hard work! Time to refuel!

 

 

Finally Dashboarding!

After searching Google Images for inspiration, we find this awesome picture of the City Circle Tram. To try and make it more palatable as a background, we used floating text boxes with shading so the map (and upcoming unit charts) don’t look too harsh against the detailed image.

 

Guest Appearance by Cynthia Andrews!

Data Rules Me stopped by to check out our viz session and gave us some great tips on making this mobile.

  • Never use floating on a mobile viz (thank goodness we had device designer)
  • Always fit width since folks are already used to scrolling

Looks pretty good IMO! We created a custom image for the top since we wanted to keep the image/banner, but everything else is just the sheets with our custom color background.

 

Final Thoughts

Overall, it was a really fun night! I didn’t expect to learn as much as I did, and its always great fun choosing design options, and thinking about where the story should go as a group!

Plus, its hard to beat this:

                

 

Until next time folks!

 

Makeover Monday – Washington Metro

Here’s my Makeover Monday contribution. For those that don’t follow the project, its where our community of data viz folks try to reinterpret and improve on an existing infographic/chart.

 

Here’s the original

 

Here’s my contribution:

How to Find Conditional Averages by Week

One interesting piece of analysis is to look at your KPIs normalized over time.

For instance, its important to know your sales in the last week, prior week, last 4 weeks.. but we wouldn’t be able to compare the results from the last 4 weeks to the last week.

But we could if we compared results all averaged to the week level of detail. Below we see results in the last 4 weeks of Superstore. We see that Sales in “TW” (This Week) were not only great, but great even compared to average performance in the last month or last 3 months.

 

Here’s how I did this:

 

Step 1: Find the last date in the dataset

 

Step 2: Find the last full week in the dataset

Also remember that the values you get from this calculation are driven by your configuration on which day is the start of the week. This gives me a start for Monday.

 

Step 3: Set up your various measure partitions by date.

Here I’m looking for dates that are 1 week in difference from my last date. Therefore, I would find the Last Week.

Here I’m looking for dates that are between 13 weeks in difference from my last date and greater than 1 week in difference. Therefore, I would find the Last 13 weeks.

 

Step 4: Create headers for your values (coming up soon)

This allows me to bin my values.

 

Step 5: Create denominators for your averages

This calculation is where the conditional average comes into play. Since we know the headers represent a certain amount of time, we can hardcode the number of weeks the value will be divisible by.

 

Step 6: Create your conditional sales metric.

We’ve already done a lot of legwork prior to this to line up the correct dates in the headers – so the sales values will naturally fall into the boolean statements we’ve already created. Same for the denominator values.

 

Its a way of looking at the business I hadn’t yet seen before but great usage of adding numbers to context. Hope you’ll get to use it soon!

 

How to Update Multiple Filters with 1 Click

I came across a dashboard at the client site with a pretty nifty trick. Big thank you to Charlotte Horgan for this innovation 😀

Ever found it annoying when you have multiple filters active on many sheets in a dashboard?

You would make your selections in 1 filter… then wait for the dashboard to update, then the next filter would have to update.. and the whole process just takes a little too long especially if you have many marks on your vizzes.

Is there a solution? Yes – with action filters!

Step 1: Establish your Level of Detail to filter your vizzes 

Create a new sheet with all the filters you want applied to your dashboard to your sheet as well as having them on detail.

Notice that Tableau has started to draw many circles? There’s exactly 11 marks to reflect the unique combinations of Regions, Subcategory and Segment in the viz.

Step 2: Create the following IF statement

iif(first()=0,”Update Dashboard”,null)

 

Step 3: Place this calculation on rows and set ‘compute using’ to cell

As I mentioned, each mark represents each unique value of the dimensions in the view. Therefore to create a “one button” experience, we’ll have to figure out how to select all the marks at 1 time.

In this case, I’ve set the table calculation’s partition to “reset” to every new value in the view. This way, the calculation will always return a value of TRUE (which is this case is “Update Dashboard”). So I will always select all the marks at 1 time. We see this is working because we’re only returning 1 header in the view.

 

Step 4: Start building your viz and set up action filters

Here’s the layout of my dashboard. The filters are from the “Update Dashboard” sheet with no filters on the other visualizations. Make sure the filters are only applying to the “Update Dashboard” sheet.

 

In mine, I have 2 action filters but only the first one is necessary to make this work.

 

Step 5: Enjoy and test your viz!

Here’s my example workbook: