2019: Week 27 Solution

You can view our full solution workflow below and download it here!

The full solution workflow.

Comparing dates against Valentine's Day

To start with we need to calculate whether each date is before or after February 14th 2019. For this challenge we’re counting Feb 14th itself as ‘Pre’ Valentines day doesn’t end until midnight that night.
To calculate this we can simply use some Boolean operators in a calculation:

[Pre / Post Valentines Day]

IF [Date] > #2019-02-14#   //If the date is after Valentine’s day…     THEN "Post"            //then label it as ‘Post’…     ELSE "Pre"             //else label it as ‘Pre’.  END

This returns the correct label of ‘Pre’ or ‘Post’ for each date. This works as Tableau allows you to manually provide dates in the following format: #yyyy-mm-dd#.

Create a Running Total of values

The step in creating a running total is to duplicate the data by creating a new ‘Clean’ step, and then joining your data to itself. In our case, we need to make sure our running total restarts for each [Store] and for whether it is [Pre / Post Valentines Day]. This means we need a join with the following conditions:

• [Store] = [Store]
• [Pre / Post Valentines Day] = [Pre / Post Valentines Day]
• [Date] >= [Date]

Joining on the dates in this way means that we end up with two date fields:

1. [Date] : This contains the date we’re going to group on when we aggregate.
2. [Date-1] : This contains each date that is less than or equal to each [Date] (within each store and pre-/post- status).

You can see this in the GIF below; as I click down each value in [Date], all earlier dates in [Date-1] get highlighted.

The more recent the [Date], the more [Date-1]s that are joined to it.

This join also generates two value fields: [Value] & [Value-1]. These are relevant to [Date] and [Date-1] respectively. In order to create our running total, we now need to use an Aggregate step with the following settings:

• GROUP on [Store]
• GROUP on [Pre / Post Valentines Day]
• GROUP on [Date]
• SUM up [Value-1]

As [Value-1] contains the value on each [Date-1], when we group on [Date] and sum [Value-1] up we’re generating the total value of all past dates, hence generating our running total!

All that remains is to just another join to attach our new [Running Total Sales] field back onto our original date using an inner join on:

• [Store] = [Store]
• [Date] = [Date]

Deleting any duplicate fields provides us with the final desired output.