1. Practical use of string and logical functions
In this demo, we will continue to work with the Fitbit data. This time we’ll investigate how well our participants follow their dietary restrictions.
In our dataset, we have the daily `Calories` per user and the “Diet details” column.
However, it seems that the “Diet details” field contains various types of information: maximum calories per day and the type of diet followed by the participant.
We want to use both pieces of information separately, so let’s go ahead and split this field, using Tableau’s built-in split function.
We’ll just right-click on the "Diet Details” and select “Transform” and “Split”. Let’s see what happens:
Two new fields are created, let’s add them to the canvas.
Tableau reads our mind! We have the two ingredients now!
Let’s have a peek at the underlying calculation:
Tableau identified repetitive elements and delimiters and extracted the right info! We could tweak that but in this case, automated split works just fine.
We'll rename these fields to “Max kcal” and “Diet”.
Ok, let’s see our participant’s caloric intake per day, We will add Sum of Calories and the Activity date to the columns.
Looking at this we are interested to count how many days with logged calories were above the prescribed caloric budget, per participant. Let’s calculate it.
We’ll create a calculated field called “Overeating” which will be a simple row-level condition, displaying 1 if above the caloric budget and otherwise a 0 and we will replace the sum of Calories with the new measure in the canvas.
Let’s see how many days they’ve had with too many calories. Since our measure is resulting in a 1 if a caloric intake is too high, we can just remove the Activity date, and sum these one values.
That’s something! But we would also like to have a count of days when the participants did not overeat, so the zeros.
Let’s duplicate our "Overeating" measure, we’ll call it “Overeating Yes No” and convert it as a dimension, and we’ll drop it to the columns, but this time we will count the days.
Almost perfect! Let’s still rename the 0 and 1 to No and Yes. You can either change your calculation, but there is an easier way.
We will right-click on the dimension in the Data Pane, and click on Aliases.
Via Aliases, you can adapt the display of the dimension values in the view.
Let’s Alias 0 as No and 1 as Yes. Note that this doesn’t impact the underlying values and only changes how they are displayed in the canvas.
This is much better:
Let’s also add the Grand total to the rows.
Finally, let’s calculate the ratio of days with overeating in total active dates.
Knowing that our first overeating calculation resulted in 0 and 1, the easiest way to do that is just to sum the overeating days and divide them by the count of the active days.
Let’s format it as percent and add the measures to the canvas. We will remove all the measures and “Overating Yes No” from the columns and we will visualize this as a box plot.
Lastly, let’s see how this differs per gender! The only field we could use, Gender/Age is a combination of two information:
Let’s extract the first letter of this field, so F for female and M for male.
Let’s create a field Gender short and apply the LEFT function on the first character.
Let’s add it to the columns and Color.
Hmm. It seems that some diets are better than others but still, the largest group cheated on the diet between 35 and 60% of the days. Not easy to keep in shape!
As you can see, using string and logical functions we have managed to extract new insights from the data.
Now onto you – let’s nail the exercises.
2. Let's practice!