Thursday, September 16, 2021

Tableau CRM : using SourceField in computeRelative node

The computeRelative node is useful to traverse across rows to identify something or calculate something.

Three important items in a computeRelative node:
  1. Partition By
  2. Order By
  3. Sort Direction (for 2 - Order By)

From the above screenshot:
- It is Opportunity 
- Partition By Account Id
- Order by CreatedDate and sort by Descending

The result in the calculated fields: 
- the first record in the partition would be the Last Opportunity created for an Account
- the last record in the partition would be the First Opportunity created for an Account
This is because we order by Descending.

There is two Expression Type in the calculated field for computeRelative: 
2. SourceField

Use Case: to identify the First or Last or Previous record

using SAQL
SAQL Expression = case when previous(Id) is null then "Yes" else "No" end
* Id in above SAQL can be any fields as it just use to check if any previous record exists.

using SourceField
In this method, the new calculated field will contain "Yes" if that's the first row, the rest will be the Id from the previous record, which is not important.

Based on my testing, using SourceField is faster than SAQL in the dataflow. In the below example, the dataflow processing close to 1.6 million rows

Monday, September 13, 2021

Happy Sweet 17teen

Oh, this is not a blog about teen turn adult, but a celebration of the 17th anniversary of my 1st Salesforce org., which is still active till now.

If this org is a human, today it is officially an adult (in many parts of countries). While the org has been evolved a lot, at least from the shape which is the user interface. I did not screenshot every year, but here are a few milestones:

super old user interface  image credit to

Theme 2 — The Salesforce Classic 2005 user interface — Its Summer 21

Theme 3 — The current Salesforce Classic user interface (a.k.a. Aloha)

The current Salesforce Lightning user interface 


Friday, September 10, 2021

Tableau CRM: Nodes with no data in Dataflow


First, it sounds weird when you said that your dataflow processed no data, but that could happen when you put a filter that clears off all data. What happened with the further nodes that create fields in the dataflow:

Augment with Right node has no data
  • Warning: The dataflow was completed, but the NodeName node didn't augment any columns. Either the node didn't find any matches, or a join key contains only null values.
  • Augment fields are created

Augment with Left node has no data
  • Warning: The dataflow was completed, but the NodeName node didn't augment any columns. Either the node didn't find any matches, or a join key contains only null values.
  • Augment fields are created

  • No error and no warning
  • ComputeExpression field is created

ComputeRelative with Expression Type = Source Field
  • No error and no warning
  • ComputeRelative field is created

ComputeRelative with Expression Type = SAQL
  • No error and no warning
  • ComputeRelative field is NOT created, because the field is not created, if you have any further transformation node that includes the new field, such as in slice, augment, etc., the dataflow will error. As per Salesforce, this is a defect, but no KI is created yet, the KI has been created, so feel free to "subscribe".

Monday, September 6, 2021

Salesforce Dashboard: Dynamic Gauge Charts

In Winter 22 release, Salesforce releases Dynamic Gauge Charts in Dashboard, even still beta. Before this, all segments (low, medium, high) values must be entered manually by editing the component. With Dynamic Gauge Charts, you just need to update a field and the segment target changed.

To use Dynamic Gauge Chart, you need to enable the feature from Reports and Dashboards Settings in the setup menu. 

The concept

You need to have a target value, this is the max value of the gauge chart. The value should be defined in an object, within a record, and in a field. Let say you use the Opportunity report for the component, it does not make sense to put the target in the Opportunity object. 

So, here is my idea:
- Create a custom object
- Create a number field: this to store the value or target
- Create a text field: this field use to store description

The component

Once "Enable Dashboard Dynamic Gauge Charts (Lightning Experience only)" is enabled, you will see Mode: Standard and Dynamic when editing a Gauge component. Standard = existing mode where you need to enter values for each segment, while Dynamic is the new one, where the segment is divided by % of target.

Object = which object to refer to chart target
Record = which record that holds the value for this component
Field = which field from the above record that hold the value

Instead of value as per normal, we need to enter % for each segment to divide segments of the low, medium, high.

Footer: once you select "dynamic" mode, you cannot make use of the footer by entering any text, it will be locked and will show the description, for example, Sum of Amount (USD) compared to Target (Dynamic: Rec-1)

  • Sum of Amount (USD) = the needle of gauge chart, which is the current value in the database
  • Target = field name
  • Dynamic = object name
  • Rec-1 = record name


Sunday, August 29, 2021

Salesforce Joined Report: Add Cross-Block Summary Formula

We shared how to add the same report type in Joined Report (in Classic) back in 2013. Despite the hate and love of joined reports, there is a feature that is not utilized by many users in the joined report, called Cross-Block Summary Formula.

Cross-Block Summary Formula is pretty similar to Summary Formula, with only different fields to be calculated from across blacks.

Not going to talk much about what is a summary formula is, but here is the sample on using Cross-Block Summary Formula. In this blog, I am going to calculate the percentage of Closed opportunities compare to All opportunities by the Opportunity Owner.

Add from Cross-Block Summary Formula, the field will be added to the selected block

Percentage number of closed opportunities compare to all.
formula B0#RowCount/B1#RowCount

My first block is filtered by closed opportunity, and the second block is all opportunity.

Percentage Amount of closed opportunities compare to all.


To move the fields to a new block on their own, add a new block and add a name field. To avoid duplication, you can remove the fields from created earlier.

Here is the complete report

Here is the result

Here are a few notes related to cross-block custom summary formulas:
  • You can add a cross-block formula to any block in the report.
  • In Lightning Experience, cross-block formulas are automatically added to the report when you create them. In Salesforce Classic, cross-block formulas aren't automatically added to the report when you create them. To add a cross-block formula in Salesforce Classic, drag it to a block.
  • When you add cross-block formulas to a block, they appear to the right of standard ones in the order in which you add them to the block.
  • The results of cross-block formulas are affected by the filter options applied to the blocks in the report. As a result, a cross-block formula can yield different results when you change filter options.
  • Each joined report can have up to 10 cross-block custom summary formulas.
  • Deleting a block that’s used in a cross-block formula also deletes the formula from both the Fields pane and any remaining blocks containing it.
  • Each cross block formula must have a unique name. However, standard and cross-block custom summary formulas can have the same name.
  • Cross-block formula names can't include brackets (“[“ or “]”).


Thursday, August 19, 2021

Salesforce: Personalize Order of items in Related List

Salesforce admin can set the order of the related list in page layout. The screenshot below is an example from the Account page.

How can I change Opportunities as the 1st tab instead of Contacts? You can personalize this for yourself.

1. Click your avatar, select Settings

2. Click Display & Layout, then Customize My Pages

3. Select Accounts from dropdown, then click Customize Page button

4. Move Opportunities above Contacts

5. Click the Save button

Now you have personalized your Opportunities in the related list above Contacts, and this does not impact anyone else.

Page-level ad