Note: This functionality/article is currently a WIP. As more Calculated Measures functionality is released, this article will be updated in due course.
This first iteration of Calculated Measures allows Orbit dashboard editors to calculate their own measures to present on visualisations. Being the first release, this version of Calculated Measures comprises of simple operators that allow you to add, subtract, multiply, and divide data variables and/or full counts of system tables together.
There are many benefits of being able to derive results from existing variables but the common case for this entry level calculation capability is ratios. Ratios find proportions such as cost per booking, or average booking per person, so that you can keep tracking key stats that inform whether you are meeting defined KPIs.
Designing Dashboards with Calculated Measures
If you are designing a dashboard and you wish to predefine calculations within a tile using Calculated Measures, then there are three concepts that you need to understand:
Data is stored in tables
Tables can be joined together in different ways
Different joins produce different results
Don’t worry, you don’t need to define, create, or adjust the tables. However, you do need to decide how you are going to join the tables within your calculation, dependent upon the results you are trying to achieve. With Calculated Measures, Apteco software attempts to do this for you.
This document is designed to help you get the result you are expecting. It takes a step back to explain the basic concepts of how data tables are related, and how the three main join types that Calculated Measures uses, impact the results that you get.
We all want data from many different sources to be in one place, with the ability to use it all to understand our customers, their purchases, and their interactions with us. To enable this insight, whatever the goal, data from different tables needs to be ‘joined’ together in a certain way to get an accurate account of activity. There are three types of joins commonly used for customer analysis.
Note: Different joins provide different results. This document explains what we mean when we talk about tables and goes on to explain three join types and provides examples of their application.
The three join types discussed are:
How to implement the different joins within a calculated measure tile
LEFT JOIN: Including all relevant records from the left table and only the related records from the right table
We do this by editing the tile where the calculation is being applied and under OPTION select ‘Change the tile to select via’. Under this option nominate the Left Table that the value being used in the calculation originates from and tick the box to ‘Return all values from the ‘xxxxx’ and any matching transaction values.
Note: The ‘xxxx’ changes to reflect the nominated table.
In the example below the table being selected is the ‘Households’ table.
Note: Avoid allowing the user to apply a filter to anything other than the Left Table or the calculation doesn’t provide the expected result.
INNER JOIN: Only include those records from the left table that are related to records in the right table, and at the same time only include records from the right table that are related to records in the left table
We do this by editing the tile where the calculation is being applied and apply two filters. The first selects all records from the Left table the second selects all record from the right table. These filters are designed to ensure that only the interrelated records from both tables are selected. In the following example the left table is ‘Households’ and the right table is ‘Loyalty Members’. A selector attribute has been chosen from each and all the categorise within those attributes (State, and Title) have been applied as a filter.
RIGHT JOIN: Including all relevant records from the right table and only the related records from the left table
This calculation happens without the need for any adjustment by the dashboard designer.
Note: Avoid allowing the user to apply a filter to anything other than the Right Table or the calculation doesn’t provide the expected result.