1. INCLUDE and EXCLUDE expressions in practice
In this demo, we continue working with the Fitbit data.
This time, we’ll investigate in detail the Steps of our participants. In the dataset, we have the users table as well as hourly steps per user.
We already have the User ID in the canvas, so let’s also add the Age bin, which we calculated in the previous chapter.
We would like to know how many steps our participants do per day. Let’s add the average Steps to the canvas. Well, we only have Steps per Hour, so we already see we won’t be able to answer this question so easily.
In this case, I'll also add the sum of all the steps as a measure, and individual activity dates to the rows.
So, the sum of steps per hour, with the Date dimension in the view gives us the answer day by day.
But if we wanted to calculate the average steps per day without having to place the Activity date in the canvas, we need to create a new calculation.
That’s when the LOD expression INCLUDE comes in handy!
Let’s call it “Average daily steps”. We’ll open a pair of curly brackets, and type in the word INCLUDE followed by a colon and a sum of steps. Lastly, we will add the dimension we want to include in the calculation, but not in the view, the Activity date.
Let’s add it to the canvas as an average!
It’s not a surprise that it renders the same result as the sum of steps per hour, but now we can simply remove the Activity Date from the canvas.
Let’s also remove the two other measures and we'll add a grand total to the columns.
So overall, our participants take about 8,330 steps per day. It would be handy to calculate this per Age group. We can check if removing the User ID will do the job.
Hmm no. Remember that with the INCLUDE expression, the calculation changes when dimensions are added or removed from the view. Let’s undo this.
To calculate this average without the User ID in the canvas, we need to edit our previous calculations. We'll do that, and now also include User ID in it.
This way Tableau will remember to store the calculation per User per day.
We can now safely remove the User ID, which leaves us with an average per Age group.
Our overall average steps of 8,336 are still correct. It could be handy to also have this total average as a calculation which we can re-use in order to, for example, compare Age bins versus the average.
Let’s calculate it in another field called the “Overall daily average”.
This time we will exclude the Age bin from the calculations of average daily steps, and we will drag the aggregation from the canvas.
Adding it to the canvas...
...and here we go, our 8,336 steps per day in one calculation!
Note that we still haven’t applied any aggregation to the final calculation. Tableau displays it as an Attribute (designated with ATTR).
This happens as there is only one single value for all the rows in the group. We can change it to an Average aggregation with no impact on the result.
LOD expressions – it's been a ride! By practicing them in various cases, you will get an even better grasp on how they work.
Mastering LOD expressions will also open up a whole new world of analytical possibilities, and will enable you to get beyond the basic calculations and obvious insights!
Now over to you – let’s do some exercises.
2. Let's practice!