Compute what? What you need to know about Compute Expression and Compute Relative Transformations

In this blog we will talk about 2 powerful transformations in Tableau CRM which are Compute Expressions and Compute Relative. We will discuss what each of these are, which use cases can be addressed with each and we will create an exercise for each of them.

Compute Expression

Compute expression is the simplest way to create a derived field, compute expression works on the same ROW, these are ROW BASED CALCULATIONS, calculating margin, same row date calculations, SAQL case statements, etc..

Use cases:

Opportunity age (number of days)- how long has the Opportunity been open or how many days have passed between today and the Opportunity created date
Custom stage order- e.g, let’s say you have 5 Opportunity stages, but based on their name they are not appearing in order, a compute expression would allow you to place them in order for example you have Qualification, Needs Analysis, Proposal/Price Quote, Negotiation/Review, Closed Won and you’d like to assign an order to each of these
Timestamp or date stamp on a dataflow so every record has a run of when it was run
A flag to identify if an Opportunity has a product associated with them yes or no.. does a product exist for an Opportunity? Yes/no

So.. let’s use use 4 and create new compute expressions on our org.

USE CASE: Flag to identify if an Opportunity has a product or not:

Create a new dataset in a dataflow with Opportunity and Opportunity Line item, Opportunity being the left source and Opportunity Product Line being the right source

In dataset builder- add your Opportunity object and augment it with Opportunity Line Item using Id from Opportunity as your left key and the Opportunity id from your corresponding line item

Your next node should be a compute expression node which you can find under the transformations as

The compute expression can be found under the transformations as *fx.* Once added you can add a name to the new field and the corresponding SAQL expression which in this case is **case when ‘Oppty_ProductLine.OpportunityId’ is not null then ‘yes’ else ‘no’ end** which is basically saying: “if a product line is found for a corresponding opportunity then flag as “yes”, if you cannot find one, then flag as “no”

Once you ad the compute expression, you can register your dataset by adding the register node and naming your dataset. NOTE: Remember the name of your dataset as we will use it to create a lens later! I am naming mine hasproduct

After your dataflow runs, open your dataset in exploration mode to see it in a lens and bring in the new compute expression and pull the appropriate flag field in: “is prod”. In this lens, you’ll see you have all opportunities that have a product line item associated to them (is prod= yes) and Opportunities that do NOT have a product associated to them (is prod=no)

New lens leveraging the new compute expression we just created!

Compute Relative

Now that’s a compute expression, now let’s try out a Compute relative,

A compute relative is useful for performing column based calculations including latest Opp by account, biggest Opportunity by country, change from previous, etc.. Compute relative is very powerful because we are creating fields that can give you previous stage, previous amount, and you can see how it changes, if it went up or down.
Use cases :

Change for previous amount and percentage- did it go up or down?
Previous stage, previous status or previous amount for an Opportunity

USE CASE: Identify Previous stage for an Opportunity

Let’s use Compute Relative how to identify the previous stage for an Opportunity

NOTE: For this use case you need to create a new dataset for Snapshot Opportunity that basically tracks every change for an Opportunity. To learn more about snapshots look at this article “Prepare Reporting Snapshots” OR add the Revenue Operations Analytics template to your org which will include a dataflow for Opportunity data snapshots that you can leverage

Once you identify the dataflow you’ll use, let’s create some compute relative fields!

You will select the compute relative transformation from the menu of transformation and add as source your Snapshot Opportunity
Node Name- > Add_computeRelative_Fields
Source→ The edgemart node (Opportunity snapshot)
Partition by→ OppName
order by→ snapshotDate
Sort→ Asc

Once you have created this- add “new field”

Then create the compute relative fields:
1) Previous Stage:
Expression Type→ Source field
Source Field-> stage
Offset Function→ Previous
Default Value→ N/A