My most used computeExpression
One thing I cannot control in the Einstein Analytics UI is the sorting of the values. It’s either alphabetically or descending/ascending values, which do make sense. Now, most of the times that’s all good, just not when you are dealing with values that happen in a certain order. As an example, I can mention opportunity stage and weekdays, where you expect a certain order and if that order is not followed you end up causing confusion because users have to spend extra time decoding what they are looking at.
If you are pulling data in from Salesforce then the solution is actually quite simple; it’s dataflow and computeExpression. While it sounds complex, it’s just about adding another node (step) to your dataflow. Okay, let’s take a step back… when you create a dataset in Einstein Analytics you choose a root object, your fields and any relationships (other related objects) you want to include. This is very simple to do in the UI. But behind the scenes a data flow is created with a set of nodes; sfdcDigest to extract an object, augment to join extracted objects and sfdcRegister to generate the actual dataset in Einstein Analytics. It should look something like this:
So how does this help you? Well by adding a computeExpression we can calculate a new field. You can use many different functions in this computeExpression, if you are interested in which, then have a look here. But in order to control sorting, you can use a simple ‘case statement’. And as the title of this blog says this is my most used computeExpression.
The very first thing we have to do is, of course, find the dataflow, so in the top right corner click the gear icon and then the data manager. The data manager is the go-to place for anything, yes you guessed it, data! In the menu select “Dataflow & Recipes” and then click on the dataflow that holds the dataset you want to change.
Next, the computeExpression should be added so click on the fx symbol.
There are two things that immediately need to be completed; the name of the node and the source. The name can be anything, but I find it best to call it something that describes what it does. I will call mine computeExpression_Sorting. The source is referring to the node that comes just before. I tend to do my computeExpression after my augments and of course before the sfdcRegister, so in my case, I’ll have the augment_User node as the source node.
We now need to create the actual field and value of it. Note you can have multiple fields in one node, so if you have a need for multiple computeExpressions no need to create multiple nodes. Click “+ Add Field”. Give your new field a name (that will be your API name) and the label will be automatically added, but you can, of course, change it. I will just call mine StageSorted and Stage (sorted). Then you need to choose your field type (text, number or date), which of course depends on what output you wish. Here I’ll keep it as text.
The last step of the computeExpression is to create your SAQL Expression. As mention above we will do a ‘case statement’. Here you need to know the input field as well as the expected values. A case statement is similar to the ‘if statement’ in Salesforce’s formula field. The syntax is like this:
case when 'API_Name' operator "Field Value 1" then "New Field Value 1" When 'API_Name' operator "Field Value 2" then "New Field Value 2" ... else "Field Value 6" end
In my scenario of getting the correct sorting for my stage field it will look like this:
case when 'StageName' == "Prospecting" then "01. Prospecting" when 'StageName' == "Qualification" then "02. Qualification" when 'StageName' == "Needs Analysis" then "03. Needs Analysis" when 'StageName' == "Value Proposition" then "04. Value Proposition" when 'StageName' == "Id. Decision Makers" then "05. Id. Decision Makers" when 'StageName' == "Perception Analysis" then "06. Perception Analysis" when 'StageName' == "Proposal/Price Quote" then "07. Proposal/Price Quote" when 'StageName' == "Negotiation/Review" then "08. Negotiation/Review" when 'StageName' == "Closed Won" then "09. Closed Won" when 'StageName' == "Closed Lost" then "10. Closed Lost" else "11. Other" end
Now you can click “Save”. If you want to create more fields in your computeExpression you can by using the “+ Add Field” or click on “Create”.
As you can see the new node is not acting as a source to our sfdcRegister meaning it is not actually a part of the dataset generated in this dataflow. At least not yet.
All we have to do is click on the right arrow on the computeExpression node and drag it to the sfdcRegister node, in my case the register_Oppty_Account.
Once done you can click on “Update Dataflow” in the top right corner and you are done. Well, don’t forget to run your dataflow if you want to see the result immediately, else your dataset will update at the next scheduled run.
Written by
The SAQL expression in not working.I am unable to get the output as told in the blog.
Sorry it’s working fine.
I am getting following error
Something went wrong while executing the computeExpression_sorting node: invalid field expression case ;when ‘StageName’ == “Prospecting” then “01. Prospecting” ;when ‘StageName’ == “Qualification” then “02. Qualification” ;when ‘StageName’ == “Needs Analysis” then “03. Needs Analysis” ;when ‘StageName’ == “Value Proposition” then “04. Value Proposition” ;when ‘StageName’ == “Id. Decision Makers” then “05. Id. Decision Makers” ;when ‘StageName’ == “Perception Analysis” then “06. Perception Analysis” ;when ‘StageName’ == “Proposal/Price Quote” then “07. Proposal/Price Quote” ;when ‘StageName’ == “Negotiation/Review” then “08. Negotiation/Review” ;when ‘StageName’ == “Closed Won” then “09. Closed Won” ;when ‘StageName’ == “Closed Lost” then “10. Closed Lost” ;else “11. Other” ;end for field ‘StageSorted’: Unknown IDTOKEN: StageName (02KS70000000rLaMAI_03CS70000000weuMAA)
Looks like you don’t have a field called StageName. You need to use the field API name from the dataset. So you can explore a dataset, click on fields in the left side. Then check the show api names and find your stage. Also it looks like you have ; in your case statement – not sure if it’s just how the error messages displays.
Hello Rikke,
I use this all the time as well. But instead i create a sequence field for anything i want to sort. So instead of 01. Prospecting, i use stagename as is but create stagename_seq and similar to your case statement but _seq just has the sort order i want. This way it keeps the field cleaner, and a standard field that may be used in many dashboards doesnt need to be replaced with the newly generated one.
How would i use a case statement to extract only records with a phone number associated to it in a project in an account object?
case statement to extract only records with a phone number associated to it in a project in an account object-
case when ‘PhoneNumber’ is null then “No” else “Yes” end
After this compute expression you can either use dataflow filter or a filter on Lens for the compute expression field.
Hi Rikke
Can we add ‘or’ operator in Case statement?
Actually i have condition is like below
case
when ‘StageName’ == “Prospecting” then “01. Prospecting”
when ‘StageName’ == “Qualification” then “01. Prospecting”
when ‘StageName’ == “Needs Analysis” then “03. Needs Analysis” .
But when i do this i get two times ’01. Prospecting’. So i want to add ‘or’ between them. But its not working.
Could you please help me out in that. or else another way to achieve this scenario.
Hi Rikke
I have tried or logic in case but it is not working can you please help me out for the this.
case
when ‘Intra_Day_Time_Bucket__c’ == 1,2,3,4,5,6,7,8 then “01-08”
when ‘Intra_Day_Time_Bucket__c’ == 9,10,11 then “09-11”
when ‘Intra_Day_Time_Bucket__c’ == 12 then “12”
when ‘Intra_Day_Time_Bucket__c’ == 13 then “13”
when ‘Intra_Day_Time_Bucket__c’ == 14,15,16 then “14-16”
when ‘Intra_Day_Time_Bucket__c’ == 17 then “17”
else “Other”
end
Hi Vishnu
Do u want create buckets in dataflow.
If so then please put ‘||’ means logical ‘or’ operator between values .
Then it will work.
Let me know if it works or u want anything other
I am trying to create a new column that is able to take a date field and essentially turn it into a text string that indicates the quarter and year based on the date being pulled (i.e., if the month of Date__c is between/includes January and March then the formula would consider it “Q1 YYYY” with the YYYY based on the year of the pulled Date. Any assistance would be GREATLY appreciated thank you!!!!