Select Page

In part 1, I discussed my own experiences of using the UP3 personal fitness tracker. Then in part 2, I loaded a year of data into Qlik Sense, the data analysis and visualisation software. In part 3, I’ll explain how to create a series of longitudinal charts showing total distance covered, active vs inactive periods, and average weight and metabolic rate.

 

If you haven’t completed the steps in part 2, you’ll need to review that post before proceeding. At the end of part 2, we left Qlik Sense open to edit the sheet, but with an empty page.

 

 

Creating Your First Bar Chart

On the left-hand side of the screen, click the first (bar chart) icon in the black bar. This opens the visualisation menu.

 

 

Click the “Bar Chart” option and drag it onto the page. Aim for the top left quarter so the shaded area shows half the sheet.

 

 

With the chart in focus (it has an orange border), the right-hand menu now allows you to make changes to how the chart is presented, labelled, how numbers are formatted, etc. First though, we need to add a dimension axis and a calculation.

 

 

Click the “Add Dimension” button

 

 

and begin typing “YearMonth”

 

 

When you see it highlighted in yellow, click the field name.

 

 

Next, click the “Add Measure” button,

 

 

and begin typing “Distance”. Again, when the value is highlighted in yellow, click the field name. As this field forms the basis of our calculation, we need to select the type of aggregation to use.

 

 

Select “Sum(m_distance)”

 

 

We now have a bar chart showing total distance covered by month.

 

Click the orange anchor point in the centre-bottom of the frame, and drag it upwards to resize the chart. Aim for around a third of the sheet height.

 

 

Stacked Bar Totaling 100 Percent

Next, repeat the exercise to drag a Bar Chart visualisation on to the sheet. Place it below the first chart.

 

 

Add “YearMonth” as a dimension.

 

Then add “Sum(m_active_time)” as the measure.

 

 

This second chart will show total time as 100%, split between active and inactive periods. To amend the chart to display in this way, we need to use the right-hand menu.

 

 

Expand the “Data” section, then “Measures”, and finally “Sum(m_active_time)”. Click on the orange “fx” to open the expression editor.

 

 

Our original calculation simply totaled the amount of active time. To turn that into a percentage of overall time, we need to divide it by the total of active + inactive time.

 

Click into the expression edit window, and amend the expression as below.

 

 

The coloured words shown should match exactly. Colour-coding indicates that field names and aggregation types have been entered correctly. Watch for the extra brackets around the entire line 3!

 

Click “Apply” in the bottom right corner to confirm the change.

 

 

The chart now shows the active time as a percentage of total time. The proportion of inactive time now needs to be added.

 

Still under Measures on the right-hand menu, click Add

 

 

Then click the grey “fx” symbol,

 

 

Back in the expression editor, enter the following expression to calculate inactive time as a percentage of overall time.

 

 

Click Apply, and now the chart shows two bars, side by side.

 

 

The final two steps mask the calculated values as percentages, then stack the bars so they total 100%.

 

Expand the first Measure on the right-hand menu, change Number Formatting from “Auto”, to “Number”, then set the formatting mask to a percentage,

 

 

Repeat for the second Measure.

 

Finally, still in the right-hand window, select Appearance, then Presentation.

 

Change the bar format from Grouped to Stacked.

 

 

The second chart is complete. Use the orange anchor-point to resize, allowing space for our final chart underneath.

 

Dual-Axis Line Chart

The final chart shows separate lines for average weight and metabolic rate. Since these measures are calculated on different scales, we need to use both the left and right axis. The standard Line chart only allows use of the left axis, so for this chart we’ll use the Combo chart.

 

Drag and drop a Combo chart from the left-hand menu to the sheet, placing it below the previous charts.

 

 

Add “YearMonth” again as a dimension.
Then add a measure, type Weight, and from the sub menu, select Avg.

 

 

A Combo chart defaults to bars. To change to lines, expand Data, Dimensions and Measures in the right-hand window, then select Line.

 

From the Measures section, click Add to add the second calculation.

 

Type “bmr”, then select Avg. Expand the new Measure, and change it to a Line.

 

 

Our third chart now shows two lines, both on the left-hand axis.

 

 

Below the “Line” icon in the right-hand window, the axis in use is shown. Change the Avg(bmr) measure, from “Primary” to “Secondary” axis.

 

 

All three charts are now complete. To use the dashboard interactively, click the “Done”button on the toolbar, then “Save”.

 

       

 

Labelling Charts, Dimensions and Measures

At this point, the dashboard is perfectly usable. However, if you plan to share the information with other users, you should consider adding titles and labels to correctly describe everything.

 

To do this, the sheet needs to be placed back into Edit mode.

 

 

Each chart uses the same x-axis dimension of “DATE.YearMonth”. This could be labelled “Period”. Click each chart in turn, expand Dimensions and DATE.YearMonth in the right-hand window, and enter the Label “Period”.

 

 

Each Measure calculation should also be clearly labelled. Starting with the first chart, expand Measures and Sum(m_distance) in the right-hand window, and enter the Label “Total Distance”.

 


The chart now shows clear labels on both axes.

 

The second chart should have y axis labels of “Active Time” and “Inactive Time”.

 

 

You’ll notice the chart legend now clearly indicates which colour refers to which calculation.

 

Chart three should have y axis labels of “Weight” and “Metabolic Rate”.

 

Our final step is to enter a title for each chart.

 

With the sheet in Edit mode, the title position of each chart is indicated with the prompt “Click to add title”.

 

 

Simply click, and type titles for each chart.

 

When you’re done, click the “Done” button, and Save.

 

Our dashboard now contains three clear, labelled charts derived from the UP3 fitness tracker data.