Einstein Analytics: Calculation without Code
When you start with Einstein Analytics the first time is spent learning how lenses and dashboards work and how to explore your data further maybe even how to get your data sets into Einstein Analytics. But there are more to the tool than just pretty graphs that facet with each other. Soon you will get the question to show the percentage change from one month to another of cases, pipeline or bike trips. This is not something that typically is in your dataset especially if you get the data directly from Sales Cloud, so what do you do? Well, this is where compare tables come in handy.
Compare tables are a lens type that allows you to grab measures and dimensions from your dataset but also calculate your own measures. Out of the box, it comes with a series of commonly used calculations like “% change period on period”, which quickly allow you to get more insight, but of course, you can also create your own calculations in the builder. Once you have your calculations done, it used to be that you were stuck with this table on your dashboard, but with one of the later releases, it is now possible to convert your table into a graph. Let us have a look at how this works by finding the percentage change of trips month on month. I am using data on the San Francisco Bike Share [find the data here].
The compare table magic
First, open up the dashboard where you want to add your compare table. In the step overview to the left click “Create Step”.
And choose your dataset, I am using my “trip” dataset.
I now see my count of rows in a bar chart, but I want to change this to be a compare table, which I will do by first clicking the table icon in the upper right corner and then the compare table icon.
Now I want to group my data by Start Date. Since I want to have a month on month comparison I will choose Year – Month.
As I want to have the number of trips I am going to keep my measure as Count of Rows. But since I will be doing a comparison I need to add an additional column. Click on the small arrow next to “Count of Rows” and then “+ Add a column”.
Now you should have two measures that are exactly the same. We will use the first column (A) for the actual number of trips the given month and second column (B) will be the percentage change from the previous month. In order to do this we will need to modify our columns, so again click on the arrow next to “Count of Rows” for your first measure and then click on “Edit this column”.
I will rename the column A to “Trips #”, click “Apply and then click the next arrow to move to column B.
I will call my column B “Change Month over Month %”. For my calculation, I will click on “f(x)” and choose “Period Over Period”. Per default, the correct column (A) and calculation type (%) are chosen. Finally, click “Apply” and “Close”.
We are not all done with our step yet. We do have the calculation done, but we have two measures in the table and I only want one for my graph. Einstein Analytics have the option of hiding columns in a table, which means I will keep my calculation but only show the measures I want to use. In order to this click on the arrow next to the measure “Trips #” and click “Hide”. Now the step is finished and you can click “Done”.
Now add a chart widget to your canvas and drag your new step onto the widget. You can now modify your chart to fit the data you are showing.
That’s it! Very simple way to calculate on the fly and present it nicely in a graph. No need to code or modify your JSON.
Written by
Hi Rikki,
Thank you for this wonderful blog, I am looking to create a size field on chart to measure my count of rows in and particular size range e.g >100 , 100 – 250, >250 . How shall I achieve this requirement ?
You can go back to your data layer and in your data flow create a computeExpression with a case statement to create this bucketing – have a look at a similar example here. This can also be achieved with recipes.
Hey Rikkie,
Thanks for sharing the useful information.
Is there any way to use bindings in Compare table (by using formula or any other way) ? If yes, then please share the syntax of it.
Thanks
Mohit
How would you compare MQLs against SQLs and display it as a conversion % using the number widget in Einstein Analytics?
It really comes down to how you define MQL and SQL.
A lead is an mql in salesforce and a SQL is a converted opportunity.
Well, the tricky bit is to understand where you get this information and how with data you define MQL and SQL. But it sounds like you can use the lead object. Create a compare table have three columns; one is MQL count of rows maybe filter by stage, second is SQL also count of rows and filter by converted opportunity (related object to lead), the third column is to do your calculation.
Hello Rikke,
sorry to bother you, i am currently working on einstien analytics project, i am building a report in there and based on Amount of Closed Lost Opportunity or Amount Value of Won Opportunity , when looking on a campaign and if there are opportunities that are related to it i want to know the values. i did a research of this formulas but i can’t find them
is there a formula field reference site you could guide me to if possible please or if this is something easy could you help me please.
Appreciate your help
Olanre
Hello Rikke,
I am trying to show a conversion rate based on each Lead Source around the Lead Status of “Qualified”. Basically, I want to take the total # of leads brought in for that Lead Source and show the % of them that became Qualified. However, when I do this compare table, I am left with % of ALL Lead Statuses, not just Qualified.
How do I show ONLY the % Qualified per Lead Source? I cannot attain this by hiding a column, or by filtering for only Qualified (It just makes turns the conversion rate to 100%)?
Thank you for your help – this has been a brain twister for even the Saleforce Support Rep helping me.
Sounds like you need to switch to saql and do a post projection filter.
Thanks! I’ll let the Support Rep I’m working with know – I’m new to this more advanced use of Salesforce reporting/analytics, so SAQL is also very new. Do you have any favorite Trailheads for learning more on using SAQL?
Hello Rikke,
I am currently stuck in finding out on how to build a formula on einstien analytics, i need to have a field that calculates the Amount of Closed Lost Opportunity value, tried to see if there is a SAQL query i can build but it does not work . is it possible to advice me ,
Thanks
Olanre
Hello I want to add column wise data in a table used in a dashboard in analytics..I want to have count of all column values in down row..so what can be done to achieve this..
This is very helpful Rikke, and maybe you have explained this already, but if I want to take this months change % and show it as a single metric “Number” widget right next to the graph, how do I isolate just that single % change number?
Hi Rikke
Can you please guide how to calculate 4 weeks of moving average.
How can i show these MoM in kpi cards, if i select month from slicer MoM data is getting null. any solution how to show the data