Workout Wednesday – 20201 | W21

I thought it was time to deliver on what the blog is promising, i.e. to from time to time provide solutions on Workout Wednesday Challenges and this time we will be looking at an – by now – older challenge dating back to week 21 of 2021.

There is usually more than one solution

In some cases solution videos or a downloadable workbook are provided by the originators of the challenge and in others there is nothing available. My solutions may or may not be the same as the ones provided (and in some cases we will not know) so take it as what it is: my approach to solving the challenge at hand.

What does the challenge include?

This specific challenge comprises – at least in my solution – level of detail calculations, parameter actions and table calculations. Also, I loved the general idea having a scatter plot that you can drill down into seeing the path that led to the specified dot’s position.

This is what it looks like – pretty cool I think

So how do we solve this?

First, lets try to define from just looking at it what we likely will need

1. An action for when we click a dot
2. A parameter that takes the clicked dot data (here: the song ID)
3. Table calculation to derive the path
4. A dual axis to be able to show the “final dot” per song as well as the path

Ok, after we have established our first idea, let’s have a look at the data that Shawn provided. We have got the SongID, two different timestamps (Central or UTC), the artist, the song and the album on which the song was released.

You will already have figured. only songID and one of the timestamps is what we need, the rest is basically for cosmetics such as tool tips.

Let’s get to work

Builing the X-Axis (columns)

We start with building our axis. The X-Axis is the days since first listening and the Y-Axis is how often a song was listened to.

Herein lies the first potential pitfall:

You might be tempted to create a calculation that defines the days difference between the first and last time listend. This, however, would not allow us to create the path we want to see.

So start with creating these two fields which we need for the X-Axis

//First playing date per song
 {fixed [SongID]: min([TimeStamp_Central])}

//--> get the first timestamp per songID

//Days since first listening - stepped
DATEDIFF('day', [First playing date per song], [TimeStamp_Central])

// --> this gives us the difference in days between the first listening and each individual time stamp

[Days since first listening – stepped] goes onto columns.

Note that so far, there is nothing that will create the dots. You can already put songID on the details and switch mark type to circle. Since currently we only have one axis this will give us a bar chart like optic but its all individual songs now.

Build the y-axis

For the Y-Axis we need to establish the times a song has been listened to. Since we only want to show the path for the song we selected, we need to now create a parameter that can hold the songs id.

So create a string parameter from songID [p.Selected song] and then create a calculated True/False field:

//TF is selected song
[p.selected song] = [SongID]

With this done we can now create our path

//Total plays selected song path
IF max([Tf is selected song]) THEN
 RUNNING_SUM(count([SongID]))
END

Put [Total plays selected song path] on the rows shelf, you can leave it to calculate table across.

You view will go blank because no song is currently selected, don’t mind that.

Let’s go on with creating our single dots, i.e. those that represent the last time a song was played and their intersection with how often they have been played by that time.

For this we must first establish the last time a song was played

//Max Date per song
{fixed [SongID]: max([TimeStamp_Central])}

Then, we establish how often a song was played

//Total plays per song - fixed basis
{fixed [SongID]: count([TimeStamp_Central])}

Now we can create the dot

//Single dot per song
IF [TimeStamp_Central] = [Max Date per song] THEN [Total plays per song - fixed basis] END

This will give us the maximum times listened to per song at the songs last time it was listened to.

Put this on rows as well, create dual axis and synchronize the axis.

Since things get cluttered, lets put the [Total plays per songs – fixed basis] on the filters, select all values and filter to 50 to 100 as the challenge requested. Hide the nulls indicator, untick show header from the right hand axis and set the remaining Y-Axis to not start at 0

Get it to work

All that is left to create the basic functionality is creating a parameter action. You can either do it on the dashboard or – if you just want to give it a try – on the worksheet itself. In either case, select our p.Selected song parameter to be fed the songID.

And that is basically it. everything that is left to do is optics such as using the TF field on the size to make the selected song bigger. Select stepped path for the line chart part. Select your colors as you like and maybe set up some further analytics like the frequency a song was played.

For example, in my solution I created another field that I put on the tooltip for the final dot which divides the number of days between first and last time listening to a song by the number of times it was listend to, effectivly yielding me an average frequency (every 6.63 days listened to this song).

Find my solution on my tableau public

https://public.tableau.com/app/profile/steffen2460/viz/wow2021-w21/Dashboard1

Hope this might be useful and as always, appreciate your feedback.

Until next time.

Steffen