It was the end of April 2023 when I got a phone call from Diego, a former and very dear colleague with great experience working with Tableau (and with data in general) from whom I personally have learned tons. He told me that he recently started directing a Data Analyst Technician Program, and that he was looking for a Tableau Trainer for one of the courses in the next cycle. 

The program had been already going for more than a year, hosted by a non-profit institution in Costa Rica, whose target group was made up by 18 young women who found in this project an opportunity to develop a complete new set of skills in a booming economic field like data analytics. Among them, one could find young mothers with little to no college background nor experience in tech fields. 

The idea of becoming their trainer really resonated me. 

The Background

The last time Diego and I worked together went particularly well. We had the privilege to work at the office of the president of Costa Rica and implement a data analytics strategy for smart public policy using Tableau. That way, we could answer many of the questions that not only the president, but also his ministries had, and remarkably improve the way public policies in the presidential house in Costa Rica were designed and decisions were reached. 

One of the last cycles of this technician program was coming, and he needed an instructor for a course in visual analytics based primarily in Tableau. He thought of me, based on our previous experience and how much we learned together that time. I turned to InterWorks to let them know of this need and to see if was something we could take on, even though their budget was very much limited. 

To my pleasant surprise, given the social mission of the institution, and the social value of the program itself, InterWorks decided to take on the project and send me as their instructor, without charging a cent, as part of our own project: “InterWorks Gives Back,” one of our attempts as a company to give something back to the society. That generous offer gave me one of the greatest teaching opportunities I’ve had so far, not only in data but in general. 

The Training

We started in late May and from the beginning, I had the pleasure to meet a group of very smart young women so eager to learn that it motivated me to develop the best course and to teach them as much as I could. They already had been learning about data for a few cycles, so they were no beginners. They even had a previous introductory course on Tableau, so that allowed me to raise the level of difficulty and demand a lot from them. We started doing three very deep sessions on Tableau Prep and develop a data-prep project with it, which I personally believe is great tool to gain some independence on the data side, and also to understand a lot more how data sources function. 

Afterwards we dove deep into Tableau Desktop and into almost every topic from the typical DI and DII courses. But besides the classes, they had to develop two not-so-simple homework and a final dashboarding project. Apart from the exercises themselves, part of what raised the difficulty level was that all homework and projects were writing and had to be presented in English. Even though the course was fully taught in Spanish (both their birth language and mine as well), we were well aware that English is key to increase their job opportunities and to improve their resume, so I implemented this in order to strengthen their contact with the language and to broaden their vocabulary. 

In our last session, they had to present their dashboards in front of the whole group and tell a short story with a couple of interesting insights that could be derived from them. Afterwards they would get comments, questions and feedback from us. That day was a particularly good opportunity to discuss and apply what we had learned about dashboarding and visual analytics best practices. 

Those hours in class, and the extra hours they had to invest outside of the course really paid off: I could see a noticeable improvement on not only their skills and abilities using the tool, but in generally understanding data. That was, for me, the most fulfilling part: to see how their understanding, skills and their personal motivation to work with data increased remarkably. 

At the end, I got not only a very sincere thanks form all the girls, but also the satisfaction of knowing that we brought something useful into their professional lives. Furthermore, I feel as though we brought even some inspiration for them to keep fighting for what they want and to draw some new life goals. 

Thanks, InterWorks and Parque La Libertad for creating this! 

The post InterWorks Gives Back: Fundación Parque Metropolitano de la Libertad Training appeared first on InterWorks.

Tableau Pulse is here! The newest development of the Tableau world, launched and driven by artificial intelligence. If you haven’t seen much about what Tableau Pulse is and how it works, check out some of the blogs my colleagues and I have already published to get you onboarded. 

Here, I’ll give you detailed and practical guidance on how to actually implement Tableau Pulse: how to enable it, how to build your metric definition, then your metrics and how to get this going into your company’s communication workflow. 

Tableau Pulse Requirements 

1. Tableau Pulse is only available in Tableau Cloud. 
2. Tableau Pulse works only on published data sources. If you need additional guidance on how to build them, check out these two detailed blogs on published data sources, and how to get them ready for Pulse.  
3. Remember to do your data prep on your published data sources before using Tableau Pulse. Your success using Tableau Pulse can only be as good as the quality of the data you’re using. 

 So, make sure you have clean, high-quality published data sources on Tableau Cloud before starting your Tableau Pulse experience. If you do, you’re ready to start exploring this exciting tool. 

How To Enable Tableau Pulse  

In order to enable Pulse, you’ll just have to go to the settings of your Tableau Cloud, scroll down a little and click on Tableau Pulse Deployment: 

You’ll have the option to enable it for all users in the site, or just for a particular selection of groups of users. Make sure you read the Tableau AI availability section. This means that you authorize Tableau to use generative artificial intelligence to analyze your published data sources and get insights from them, which is one of the core elements of Tableau Pulse.  

Regarding permission: Creators, Site Administrator Explorers or Explorers (users who can publish) are all able to create metric definitions. While the ability to see the data for a metric depends on access to the data in the data source that the metric is connected to, Tableau Pulse doesn’t prompt users to sign in to the database or data connection for the data source.  

Once activated, you’ll see a new navigation button for Tableau Pulse on your left-hand side panel. Click on it: 

The Tableau Pulse Environment 

Tableau Pulse has a completely different environment than Tableau Cloud. You’ll be redirected to it: 

If this is your first time visiting Tableau Pulse, your screen should appear mostly empty. That’s because Pulse will show in the first tab those metrics that you’re following. You can also click on the second tab, Browse Metrics, to check any existing previously built metrics that you could start following. We’ll get back to this point. 

At this point, we’ll assume that no metric definition has been created and, as a result, no metric is to be found yet in our Pulse environment. 

Metric Definitions and Metrics 

To start building content, we need to build metric definitions, which is the foundation of Tableau Pulse. And for that, two concepts must be crystal clear: 

A metric definition is a set of rules and lays out the base of metrics. We need first to build the definition and from it, we can build the metrics themselves. So, with a metric definition, we define the data source of the measure that we will analyze, the measure itself and its corresponding time dimension. 

By establishment of a metric definition results in the creation of a chart, with a KPI and a Sparkline. That’s your first metric: a metric with no segmentation. By modifying the scope of that first metric through the use of filters, we can create other metrics, which are always a special cases of that first metric created and that are always restricted to our original definition. 

The following chart summarizes these ideas: 

Note the “Technology” and “Office Supplies” are filters on the original metric definition creating two new metrics. 

Create a Metric Definition 

The process of creating a metric definition is relatively simple. However, it is worth to pay attention to all details and options, to make the best of them. Here are the steps, one by one: 

1. Click on “New Metric Definition:” 

2. Select the published data source:

Now we are in a new interface. Notice the two areas. A left-hand side panel where we will create the definition (blue selected) and an empty canvas, where our first metric will appear, once created the definition (purple selected):

Notice that our panel has actually two sections: definition and insights. We will need them both. We’ll start with the definition, of course:

Remember: our definition possesses three essential elements: data source, measure and time dimension.  

3. Now select the measure and add a corresponding description. I’ll show you using the example of admissions in accidents and emergencies services:

4. Now you must select the measure we will analyze in this metric definition. It has to be aggregated to some level: sum, average, count distinct, for instance. The typical aggregations in Tableau. The aggregation could also be a running total or non-cumulative. You have the option to add a definition filter, which is different from the filters that the users can use. This one will reduce the scope of the definition itself:

5. Next select the time dimension and the comparison periods, which could be prior year and/or prior period:

6. Those were the essential characteristics of a metric definition. Next steps are optional, but also desirable. Add adjustable filters. These can be manipulated by the end users and, through them, they can create new metrics from the definition. They will appear on top of the metric chart:

7. Final step on this is to select the format of the measure shown and add the terminology in singular and plural for the elements that we are analyzing in this metric definition. It will let texts become more natural in language:

The panel on the left-hand side is the metric definition, the chart itself is a metric:

It might appear that our metric is ready, but we still have more options to perfect our definition and adapt it to more what we’re looking for.

Metric Insights

8. Afterwards, go to the Insights section. Here you will be able to set an interpretation of if it’s favorable or not that the value increases. This will improve your insights and will better guide the AI behind Pulse. I’ll show you with two examples: sales, whose values going up is positive, and admissions, which is quite the opposite:

9. Plus, you can decide turning off any of the insight types Pulse offers. Tableau gives you the relevant information to decide. You’re able to deactivate those who you don’t find relevant. These are the types of insights Tableau Pulse creates:  

1. Risky monopoly: show dimensions with a concentration of very high values.
2. Top drivers: shows which values for a metric that increased the most across a specific time offset.
3. Current trend: shows current trends to communicate the rate of change direction and fluctuations for the metric value.
4. Bottom contributors: shows the lowest values in a dimension for a metric within a given time range.
5. Top detractors: Shows the values for a metric that decreased the most across a specified time offset.
6. New trend: shows new trends to communicate the rate of change, direction and fluctuation for the metric value.

After this you are finally ready to hit the Save Definition button: 

Again, you’ll see your first metric, or metric without segmentation, on your right-hand side. 

Use a Calculated Field as a Metric Definition

If the field that you’re using is not exactly the one that you need, you have the option to create a calculated field by using the advanced definition menu: 

There, you’ll have the option to model, similar to when you open a published data source: 

And you’ll have the option to create a calculated field: 

In this example, I’ll create the classic Profit as % of sales: 

Next, you’ll have to add the new field to measure and then add a date field. Tableau will create a chart to visualize the result. It’s not actually the metric though. You can add a filter, which is not the same as the filters for the users, but a pre applied filter to reduce the scope. In other words, a definition filter, if wanted: 

When you’re done, click on Save Definition, and the new metric definition will be created:

Now, you’ll notice that your measure in the definition is our newly created calculation. And again, what we see in the visual on the right-hand side is our metric without any segmentation:

Don’t forget to edit the format of the measure and also its insights, like we saw in the previous section:

Explore the Metric and Create Additional Ones

Once your metric definition is ready, you’ll land on a page like this one: 

Here is our initial metric, without any segmentation. The data will always be referenced to today and by default set to month to date. For any other time segmentation, use the time filter. Explore it. Notice that the terminology we added, helps the selected summary become more natural in language. This is also generated by AI: 

Once you select a different and particular segmentation, click on the blue check mark. The filters will be applied and the chart changes: 

And just like that, you’ve created a new metric. You’ll noticed that now a new metric appears, according to the filters you applied. In this example, the admissions for male patients, older than 65 years old: 

Explore the breakdowns too. Pulse breaks down your metric according to the filters you added to the definition, so those are worth analyzing, by clicking on one or another. In this example, we have Market and Country: 

Afterwards, explore the insights. This is where the generative AI of Tableau Pulse enters. They are generated in your data sources and AI helps translate it into natural language:

You’ll see the different type of insights, according to those you let activated in the definition and those who makes sense to your data. Click on a suggested questions to add more insights: 

Additionally, you can give feedback to Tableau on the summaries. 

Get Tableau Pulse Into Your Company’s Communication Flow

 As important as creating your metrics is to subscribe to them and to add the digests into your channels. This enables communication, and completes the whole process of creating meaningful insights through the power of AI and communicating them to the people who make decisions. 

So, the first step is to subscribe to the metrics that are relevant for you:

All those metrics will now appear on your Pulse starting site: 

The second step is that Tableau Pulse prepares a digest, where it will show all your subscribed metrics. Could be sent daily, weekly or monthly. Remember, however, that Pulse updates daily. So, go to Preferences on Pulse: 

And define the channels where you’d like to receive this digest, like an email or a message on Slack, and its frequency:  

If you choose Slack, don’t forget to install the Tableau app in Slack. You may need to ask your Slack admin to add this: 

Your digests will arrive early in the morning through the selected channels to all those subscribed, and thus completing the analytics process we started by setting up Pulse and creating our definitions, with the always important effective communication. 

I hope all Tableau users out there can make the best use of this powerful tool. If you need support implementing your data and AI strategy in your business or institution or assessing potential benefits of AI, please reach out to us. We’ll be glad to offer you technical support and guidance. 

The post How To Build the Best Metrics in Tableau Pulse appeared first on InterWorks.

As we’ve published before, a funnel is a great way to represent and track a flow or stages in a process. Whether in business, science or engineering, for instance, we can use funnels to easily see and analyze how large or small those stages are, or, in other words, how many elements are reaching each certain point. We can talk about conversion rates (how many elements are we able to transform from one phase to the next one) or find out if there are any sort of bottlenecks within our process:

On past articles, we’ve shared how to build several types of funnels: a stepped funnel, a smooth funnel and a shape funnel. Now we are upgrading them by building a more complex version: a colored smooth funnel. 

A funnel whose sections have all individual colors and whose dimensions of those sections are in total proportion to the amount of elements. We will add not only the number of elements to each section, but also the percentage they represent respect to the initial phase. In other words, the conversion rate. Each one of these steps requires tools, like table calculations and level of detail calculations (LODs). Haven’t you worked with LODs before? No problem! We have a great guide for you here. 

These steps might not seem that simple or intuitive, but don’t you worry. We’ll guide through the whole process. 

The Data Set 

The steps shown in this post use a generic set of data with very few fields. We require: 

• A field that identifies the elements that we are counting. It could be accounts, clients, projects, products or subjects, but in this case, we’ll call them leads, and the field itself, Lead ID. 
• A field that identifies and assigns ordinality (we’ll go back to this term) to the phase where the element or lead is (or where it has been). This means this field must to be an integer. In this case, Phase ID. 

About this second field, consider two aspects: 

First, ordinality means order. This field should tell us in which order the phases occur. Second, the section in parentheses is optional. We’ll see that we can build this funnel using the phases where every element has actually been, or we can build it only using its current phase and assume that that element has been on all previous phases in order to get there. This means, we’ll be able to build this funnel whether your data has the history of all phases where an element has been, or even if your data only has the current or latest phase of every element. 

All other fields, like name of the phases, dates or other measures are nice-to-haves, but not required, other than the first two mentioned. 

Now let’s dive into the actual building! 

The Calculated Fields 

We will need several calculated fields. To be precise, three by every phase of our process. 

1. The first type of field will retrieve the number of elements per phase: 

{ EXCLUDE [Phase ID] :  
COUNTD( IF [Phase ID] >= N AND [Phase ID] < N_max THEN [Lead ID] END )}

Where N is the phase, whose elements we are counting, and  N_max is the last phase; in case the last phase is something similar to close lost or unsuccessful. 

The logic of this field is as follows:  

• We want to count all leads in a particular phase. Hence the first condition after IF. 
• We also want to include elements in next phases that might have skipped a particular phase, but exclude those in the last phase, as they are unsuccessful. Hence the second condition after AND. 
• And we need the result to appear in the chart, no matter which phase we are plotting. Hence the function EXCLUDE. 
• We will name this field Phase N Leads. Changing the N to the actual number of the phase. 
• In case we just want to count the elements that are actually in the phase, the field can be simplified as: 

{ EXCLUDE [Phase ID] : COUNTD( IF [Phase ID] = n THEN [Lead ID] END )}

2. The second type of field will let us plot the result of the first calculated field in the actual funnel: 

IF ATTR( [Phase ID]) = N THEN SUM( [Phase N Leads]) 
ELSEIF LOOKUP(ATTR([Phase ID]),1) = N THEN SUM( [Phase N-1 Leads]) 
END

Where, again, N is the phase of the field we’re building, and N-1, the previous one. 

About this second field: 

• It contains a table calculation within it. 
• It retrieves two results: the number of elements of the current phase and the elements of the previous one. That’s how we get the polygon shape for every phase in the funnel. 
• Consider that, since we have the term N-1, we build this field only for the second phase onwards. The first phase doesn’t require one. 
• We can add this field to the label mark to be able to read the actual value. 
• We will name this field Phase N.1, as N represents the corresponding phase. 

3. The third type of field is very similar to the second one and you must build it, only if you want to also show the percentages each phase represent respect to the initial one, i.e. the conversion rates. If not, you can dismiss it. 

IF ATTR( [Phase ID]) = N THEN 
SUM( [Phase N Leads]) / SUM( [Phase 1 Leads]) 
ELSEIF LOOKUP(ATTR([Phase ID]),1) = N THEN 
SUM( [Phase N-1 Leads]) / SUM( [Phase 1 Leads]) 
END

Remember that in this field, we compare every phase to the first one to get the desired percentage. It can also be added to the label mark. We will name this field Phase N.2, as N represents the corresponding phase.  

Don’t forget to add the corresponding default format to each field (whole number and percentage). This will save you some minutes when building the actual funnel. And remember to repeat the calculation for every phase, simply by duplicating the first of each one and changing the fields inside. 

The Actual Building of the Funnel 

1. Use the image above as a guide. We’ll start by adding in a new sheet the following: 
   1. Phase ID into Rows. 
   2. Measure Values in Columns, with the values of the second set of fields we created (Phase N.1) as measure values. 
   3. Measure Values also in the label mark. 
   4. Measure Names in filters, as usual. 
   5. Measure Names also in the color mark, so we can assign a different color to each phase. 
2. Next, we’ll need to change the scope and direction of our calculated fields in Measure Values to be Table (down) (remember that they are table calculation actually). 
3. You will get double values in the labels. Make them overlap by aligning them equally instead of automatically so that they appear to be just one value per phase. 
4. If you want this section to be the right-hand side of the funnel, duplicate it, and after that, invert the axis, so that it goes from right to left. 
5. Continue with the duplicate sheet and replace the fields in Measure Values with the third set of fields we created (Phase N.2). The ones that contain the percentage values. 
6. Repeat the process to change their scope and direction to Table (down). 
7. You should get a shape identical as the previous sheet, but this time the axis has values only from 0 to 1. 
8. Align the color, so that they match the previous chart, and add the values to the label:
   
   
9. Combine the two sheets in a dashboard to build the complete funnel. 
10. Add the phases to the labels and hide them as headers, hide the axis, edit the aliases of the legend, hide the zero line of each chart and customize everything accordingly:
    

There you go! A smooth funnel with a color for every phase that shows both the number of elements and their share compared to the initial point. If you want help implementing this for your dashboards, feel free to reach out and see what we can do for you.

The post How To Build a Colored Smooth Funnel in Tableau appeared first on InterWorks.

Tableau Pulse is coming — The newest development of the Tableau world, launched and driven by artificial intelligence. Its main goal: To reach that 70% of people within companies that don’t have the word data in their work titles — people who don’t necessarily know how to build a dashboard or have the time to do it. In other words, to keep making date easier for everyone.

The aspiration is to achieve this in a personalized, contextual and smart way by transforming outcomes with metrics that are relevant for our businesses, identifying and communicating insights with AI and infusing data into our flow of work.

How Does Tableau Pulse Work?

Its ground architecture has the following three layers:

1. Metrics layer: where important KPIs are located, enriched from a single source.
2. Insights Platform: where patterns and changes in metrics are automatically detected.
3. Next-Gen Experiences: where contextual insights in our flow are digested.

All of them enhanced by Tableau AI.

Let’s dive a little deeper on each one of these layers:

Metrics Layer

In this layer, we’ll need to define our metrics as our scalable & manageable source of truth.

They possess the following characteristics:

• Measure and Aggregation (and likely some calculations) that hydrate the metric with data.
• Time Dimension and Temporal Level of Detail to track and filter the metric over time.
• Definitional Filters required for the metric to be right (e.g. closed_won = True for “Bookings.”)
• Related Dimensions along which the metric can be meaningfully broken down.
• Additional Metadata and Relationships like “up is good,” or pointers to other metrics or analytics content to help contextualize the metric.

A metric is an easy, understandable analytics artifact. Some examples are:

• Metric Value that provides users “the number” to orient on.
• Sparkline and Trend to show how the metric is performing over time.
• Scope like “this metric last month, broken down by this dimension.”
• Insights that create the “aha! moment” for users.
• Actions that can be taken against that metric, integrated with the tools available across the portfolio.

Changes to a metric definition affects all related metrics.

Insights Layer

This second layer is where the magic really happens. It’s where the analysis of the behavior of a metric takes place. It goes deeper into the data and it’s easy to understand for stakeholders.

It communicates through these tools:

1. Insights Generation.
2. Insights Ranking: Just the most important insights are communicated to the user.
3. Insights Summaries (enhanced by generative AI): Communicates the most important and impactful metrics with business-friendly language for the user.
4. Follow-up Questions: Templated questions to guide the user.

Next-Gen Experiences

Finally, the third layer integrates the insights and metrics into our flow of work and, with natural language, communicates the way we want it to (whether it’s email, Slack, mobile or desktop) so that it will be digested when and where it’s needed.

The post Take Your Pulse: How Does Tableau Pulse Work? appeared first on InterWorks.

For those businesses that have a portfolio of clients, or a variety of members or students, a frequently asked question is, “For how long have I worked with my clients? For how long have these people associated with my business or service?” In order words, “What’s the tenure of each one of my clients?”

Calculating the tenure of each of the clients in a portfolio can already be a challenge in Tableau, but we have a relatively straight forward fix for that: for example, in the case in which we have several different contracts spanning different periods for each customer, a FIXED calculation taking the first date of all contracts per client and comparing it to the end date of the last contract does the trick: 

Tenure Start Date: 

{ FIXED [Client]: MIN([Start Date]) }

Tenure End Date: 

{ FIXED [Client]: MAX([End Date]) }

And then it would be just a matter of calculating the difference between those two fields, in months for example: 

DATEDIFF('month', [Tenure Start Date], [Tenure End Date])

(In case you have never seen an LOD before, like the FIXED here, jump here!) 

There are other use cases: students who get enrolled in certain periods, or memberships in a particular club, or for how long our colleagues usually stay in our company.  

The Problem  

What happens when this tenure is considered to be disrupted because certain clients have been away for too long? In other words, if a certain period of time goes by in which there was no contract (or no enrollment, or no membership), we might want that the tenure be restarted and just count those contracts for which the gap between them is not too large. 

In this case, the solution becomes remarkably more complex, because the starting date of our tenure now is not necessarily the starting date of the first contract — it might be any contract, depending on how large the gap between them is. 

But don’t worry! In this blog, I’ll guide us through a general solution to this problem, independent of the number of clients or contracts, and from the size of the gap to consider a tenure to be interrupted. We’ll use the term client and contract, but as explained earlier, it also applies to students and members, and to enrollments and memberships, for example.  

I have put together 11 steps, four in Tableau Prep and seven in Tableau Desktop. Yep, that’s a lot, but again: don’t worry, we’ll get through that and you don’t have to remember everything by heart. That’s what this blog is for!  

(Also: We could just as easily do this on Tableau Cloud, since we can find Tableau Prep there as well.) 

Let’s dig in!

The Solution

Part A: Data Prep in Tableau Prep 

The original data source must contain at least the following fields: 

• Client (name and/or ID) 
• Contract ID. 
• Start date of the contract. 
• End date of the contract. 

We expect also to have one row per contract. The complete flow in Tableau Prep will look more or less like this: 

The objective with Tableau Prep is to build a new field: the size of the gap between the end of one contract and the immediate next one. For that, we will need a field that shows the ordinal number of all contracts within a client (meaning: if it’s the first one, the second one, the third one, etc., also called ordinality) and a field that contains the starting date of the next contract. These steps might also be done on the database side to bring the size of the gap directly to Tableau. 

Step 1: Create two calculated fields: “Contract Rank” & “Contract Order” (Clean 2) 

Connect your data set and create a first calculated field named “Contract Rank” with the following logic: 

( { PARTITION [Client]: { ORDERBY [End Date]  DESC: RANK()}} )

Then in a second field, use that rank field for this calculation, that we call “Contract Order”: 

([Contract Rank] - 1) * (-1)

This first field will assign the ordinal number to the contracts of each client based on their end dates. Then, by subtracting 1 and multiplying by -1, the other field assigns a 0 to the current contract, a -1 to the previous contract, a -2 to the contract before that and so on. 

Step 2: Split the flow and modify the previous calculated field (Clean 3 & 4) 

Here we use a cool trick: we add another cleaning step, and put it below the other. With that our flow diverges  into two paths, which we’ll unify again later on. Add two clean steps from Clean 2. One will have no changes, and in the second, create another calculated field “Prev Contract”: 

[Contract Order] - 1

This way we will be able to do a join of the table with itself, assigning each contract the start date of the immediate next one. Erase all fields except for [Prev Contract], [Client ID] and [Start Date], and rename that last one as “Next Start Date”. 

Step 3: Build the Join 

Build a join between the steps Clean 3 and Clean 4, using first [Client], then [Contract Order] and [Prev Contract] as keys. It must be a left join, so that the contracts with our calculated rank with 0 will not be filtered out. (There won’t be zeros there in our field [Prev Contract].) 

Above: The necessary settings.

Step 4: Calculate the gap between contracts and create the extract 

We will use the end date of each contract and the start date of the next contract to calculate this gap. For the case of the last contract (with our rank 0), it will be calculated against TODAY. That way, if even the last contract is too old (the gap is too wide), it might be discarded and no tenure calculation be performed at all. 

CASE [Contract Order]
WHEN 0 THEN DATEDIFF(‘day’, [End Date], TODAY())
ELSE DATEDIFF(‘day’, [End Date], [Next Start Date])
END

The flow should look similar to this: 

Finally, create the output (here we go with an extract) and connect to it in Tableau Desktop. 

Part B: Calculated Fields and Parameter in Tableau Desktop 

Now, that our data is set up in a usable way, we can start on the analytics side to solve our challenge. Everything down here is done in Tableau Desktop, but of course, this would also work in Tableau Web-Edit directly on the Tableau Server or on Tableau Cloud. 

Step 5: Gap size parameter 

Create a parameter that we are going to need for the next step. Use following settings: 

Step 6: Create a calculated field “Contract connected” 

This fields shows if the specified contract is connected to the next one depending on the size of the gap that we calculated before in Tableau Prep. Our parameter defines how big this gap may be. (In our example here: 100 days.):

IF [Gap between contracts] <= [Gap Size (Days)]
THEN “Connected”
ELSE “Disconnected”
END

Step 7: Create a calculated field “Connection lost contract” 

This field will show the [Contract Order] if the contract is not connected. This is key, because this is the way we can identify the first contract in which the connection was lost, and up to that moment, the tenure should not be calculated any further: 

IF [Contract connected] = “Disconnected”
THEN [Contract Order]
END

Step 8: Create a calculation “Number of contracts to include” 

With this field, we can extract for which contract we first lost the connection to the next one. And then by multiplying its rank with -1, we get the number of contracts that should be taken into account. (The second part of the code.) If the connection was never lost, it means all contracts should be taken into account, so one has to simply count all contracts for that client (first part of the code):

IF ISNULL( { FIXED [Client] : MAX([Connection lost contract]) } )
THEN { FIXED [Client] : COUNT([Contract Order]) }
ELSE ( { FIXED [Client] : MAX([Connection lost contract]) } ) * (-1)
END

Up to this point, we would be able to build a table with the calculated fields to get a glimpse of what we’ve built: 

Step 9: Create a calculation “Tenure Start Date” 

For every client, we will get the starting date of its tenure by extracting the start date of the contract, whose order is the same as the number of contracts to include plus one, times minus one. E.g. if the number of contracts to include is three, then we would take the start date of the contract -2 (this would include contracts O, -1 and -2) and leave the rest outside: 

{ FIXED [Client] : MAX(
IF [Contract Order] = ([Amount of contracts to include] * (-1) + 1)
THEN [Start Date]
END
)}

Step 10: Create a calculation “Tenure End Date” 

The end tenure date is simply the end date of the last contract (or contract rank 0.) The second statement will exclude clients whose number of valid contracts is zero. That means that even the last contract is considered old and not valid anymore: 

{ FIXED [Client] : MAX(
IF [Contract Order] = 0 AND [Amount of contracts to include] > 0
THEN [End Date]
END
)}

Step 11: Create a calculation “Tenure (months)” 

LAST STEP! The tenure we are actually after, calculated by using the two respective dates we produced in the last steps: 

DATEDIFF(‘month’, [Tenure Start Date], [Tenure End Date])

With that, we get our final table! It looks like this:

  And of course, we can put it into a sheet so it looks a bit more readable:

Or even build a bar chart that visually shows when the tenure started and ends: 

That’s it! If you want to work with us on further Tableau projects, contact us.

The post Tenure Calculations in Tableau appeared first on InterWorks.