Identify ‘Rising Stars’ using Trend Indicators

Do you ever find yourself challenged by finding and working with ‘the most trending XYZ’, where ‘XYZ’ is the product, case reason, topic item, or technically spoken any other variable? This blog post will address and help you solve this challenge by using the descriptive capabilities of Tableau CRM (meaning it’s all point and click) and the concept of trend indicators.

What is trend analysis?

Scientifically, a trend analysis is a technique that is used to determine and analyze changes occurring over time or related to time. More specifically, it compares the values of different periods to mark an increasing trend (numerical values increasing over time), a decreasing trend (numerical values decreasing over time), or simply no significant change (even if the values are changing from one data point to the other). When looking at this technique from a ‘business context’ point of view, we often use trends to determine future movements of data and to define our actions on how to deal with these changes.

There are many different ways to create a trend line and therefore, this can be considered a topic on its own which is clearly worthy to be addressed with a separate blog post. As mentioned before, this post specifically looks into the following question: What are my rising stars?

The Challenge

Let’s imagine the following scenario; we are working for a travel agency and want to find the destination provided by the agency which experiences the highest increase of passengers and travelers. This would enable us to give our customers recommendations and suggestions for their travel plans. This could help the agency increase their customers’ satisfaction and in overall profit and expansion of their business. 

In most cases, analytics users would start off by creating a line chart, displaying the number of passengers per month and their destinations. In Tableau CRM, this graph would look like this: 

When looking at the chart above, it is easy to determine that Cancun, Mexico is the most frequently visited destination with a high absolute increase in passengers, along with other destinations which experience a high volume of passengers. Now, let’s take a closer look: the chart makes it quite hard to determine the trend of ‘less popular’ destinations, even if they have a heavily increasing trend – like the destination of Amsterdam, where the number of passengers increased from about 8k in August to 42k in December.

This means it is now more convenient to ask a question such as ‘what XYZ of my hundreds or thousands of items had the most and stable increase over time?’ rather than continuing with our initial question ‘what is the trend of XYZ?’

The Concept

Now, let’s give some consideration to certain trending destinations and ask ourselves, ‘what do we really want to find out?’ or ‘What data do we really want to draw from our analysis?’
We want to determine destinations that are increasing in popularity, meaning they should have experienced an increase in booking requests from one month to the other. 

To make this visible, we are introducing a new KPI, the trend indicator: for each month a destination has been booked at least 10% more often than the week before, it’s getting +1 for the trend indicator, everything between +10 and -10% change from the previous month it’s getting 0 points, and all destinations decreasing by more than 10% are getting -1. As we look at a period of five months (4 months ago to current month), the trend indicator can be between -4 (decreased every month) and +4 (increased every month). With that said, we can find destinations with a steadily increasing or decreasing trend.

Well, how will we do this practically in the program?
Let’s do it step by step:

Preparation

Before we can start creating our trend analysis dashboard, we have to make sure that we have proper data available. For this case, we can work with either very granular or pre-aggregated data, as long as we have a measure (like sum of passengers) by time (day, week, or month) and at least one dimension including the item (in our example the destination). Additional dimensions (like destination country or carrier) will make the dashboard more valuable.

As an example, we’re using demo data, based on some real data from Transtats. In this dataset we have the number of passengers per month, carrier, origin, and destination:

If you want to follow the next steps hands-on, please upload this file into a Tableau CRM dataset.

Note: Please make sure to change the ‘Id’ columns to type ‘dimension’.

Create a Compare Table

To start with the Trend Analysis, we have to create a new blank dashboard and save it as ‘Trend Analysis’ dashboard. From there, we create a query based on our dataset ‘Flights Data Preped’ in compare table mode which presents the sum of passengers. In this query, we will use the column-specific filters (only available in compare tables!) to create five columns with sum of passengers for the different months. If you have current data, which will be refreshed regularly, you can work with relative dates (like in my example). Otherwise, set the filters based on absolute dates to define the column filters:

To keep track of the query, we will edit the column names to M0, M-1, M-2, M-3, and M-4; where M0 indicates the current month, M-1 indicates the last month, and so on.

Finally, we can group the compare table by destination:

Calculate the Trend Indicator

This next step is the most important in the concept: we will calculate the trend indicator.
As mentioned above, the logic is:

If the change from M0 to M-1 is greater than 10%, the result is 1.
If the change from M0 to M-1 is less than -10%, the result is -1.
Otherwise, the result is 0.

Translating this rule into Tableau CRM syntax and applying it to our compare table, we get the following formula for the first part of the trend indicator:

case when (A-B)/B > 0.1 then 1 when (A-B)/B < -0.1 then -1 else 0 end

We can add a formula to the compare table, label the header as ’trend indicator’ and apply the formula created above.

This seems to work perfectly, however, a closer look into the data will show, that there’s a little challenge left: we have 0 and null values which result in null values, so the trend indicator will be 0 (because it’s the “else” value). But what if we have an increase from null to 4, like for the destination of BER?

As this is completely correct in terms of mathematical calculation, we have to define a business rule: if the previous month in our comparison is 0 or null, then the trend indicator should result in 1 – if the current month is 0 or null, then it should be -1, if both of them are 0 or null, the result should be 0.
When we apply the logic into our syntax, one possible solution would be the following (there are other ways to solve the challenge, of course):

case when (A-B)/B > 0.1 then 1
when (A-B)/B < -0.1 then -1
when (B is null || B == 0) && (A is not null && A != 0) then 1
when (A is null || A == 0) && (B is not null && B != 0) then -1
else 0 end

This is the formula to calculate the change between M0 and M-1. Based on that, we have to adapt the formula for the other three changes between M-1 and M-2, M-2 and M-3, M-3 and M-4.
Lots of copy and pasting with some minor changes in the formulas should give the following:

case when (A-B)/B > 0.1 then 1
when (A-B)/B < -0.1 then -1
when (B is null || B == 0) && (A is not null && A != 0) then 1
when (A is null || A == 0) && (B is not null && B != 0) then -1
else 0 end
+
case when (B-C)/C > 0.1 then 1
when (B-C)/C < -0.1 then -1
when (C is null || C == 0) && (B is not null && B != 0) then 1
when (B is null || B == 0) && (C is not null && C != 0) then -1
else 0 end
+
case when (C-D)/D > 0.1 then 1
when (C-D)/D < -0.1 then -1
when (D is null || D == 0) && (C is not null && C != 0) then 1
when (C is null || C == 0) && (D is not null && D != 0) then -1
else 0 end
+
case when (D-E)/E > 0.1 then 1
when (D-E)/E < -0.1 then -1
when (E is null || E == 0) && (D is not null && D != 0) then 1
when (D is null || D == 0) && (E is not null && E != 0) then -1
else 0 end

To have some more Indicators used for sorting in the dashboard, we want to calculate the change in % between M0 and M-1 as Delta-1 and M0 and M-4 as Delta Total. For those cases, where 0 and null values are included, we want to apply the same logic as above.

Formula for Delta-1:

case when (B is null || B == 0) && (A is not null && A != 0) then 1
when (A is null || A == 0) && (B is not null && B != 0) then -1
else (A-B)/B end

Formula for Delta Total:

case when (E is null || E == 0) && (A is not null && A != 0) then 1
when (A is null || A == 0) && (E is not null && E != 0) then -1
else (A-E)/E end

Note: Make sure to select ‘Percentage’ as number format.

Finally, we can switch back from compare table mode to chart mode and visualize all values in a bar chart:

Arrange the dashboard

After completing the calculations, the next and most ‘fun’ part is to arrange everything on a dashboard. 

First, we drag the query onto the dashboard. Make sure to leave some space at the top and use the full available width of the dashboard. 

The legend isn’t required as all the measure names are at the top of each measure – waiting to be used to sort by them.

We can use additional dimensions like the ‘carrier’, the ‘origin’, or the ‘destination country’ to allow the user to filter down and specify certain data points.

With a ‘range selector’, we can make sure that we get results only for destinations, where the minimum number of passengers have been going to per month.

Use the dashboard

After choosing your filters, you can 

• sort by the ‘trend indicator’ to find destinations with a steady increase of passengers.
• sort by ’Delta-1’ to find destinations with the highest increase of passengers from previous month to current month.
• sort by ‘Delta Total’ to find destinations with the highest long term increase of passengers, from 4 months ago to current month in our example.

Trend Indicator

The concept of a’ trend indicator’ can be adapted and applied to many different use cases, where you have a high amount of ‘items’ and want to identify those with a steady trend. Just to name a few examples:

• What case reasons are appearing more frequently? → To find solutions for cases before they are going to grow into an even bigger issue.
• What sales territories are evolving best or worst? → To identify, from which sales teams we can learn and which of them need more support.
• What are the (technically complex) products with the highest increase of quantities sold? → To allow partners to skill up their resources for the right products.

To adapt the logic to your needs, you can play with:

• the periods: years, quarters, weeks or days instead of months
• the number of periods: it can be more or less than four comparison periods
• the thresholds of the trend indicator: it doesn’t have to be 10% of change

Once you have done the calculation, you can easily change the grouping in the query – e. g. from ‘destination’ to ‘origin’ – or make it even flexible using bindings.

Hope you found this blog useful!

Written by

Roman Michalik

I am a Principal Solution Engineer of Tableau CRM.
From the very beginning of my business life, I focused on the business perspective and customer-related context of reporting, dashboarding, and analytics.

See author's posts