Einstein Analytics: Transpose data from columns to rows

In the table-1 below; Data-1, Data-2, and Data-3 are stored as individual columns.

Here is what we can get from Table-1

But, we can't have a total of A, B, and C as a single bar chart for easy comparison, the easiest option is to change the dataset into Table-2.

Solution: use dataflow to expand the rows and create new fields with combine values

Notes:
- ceData1,ceData2,ceData3 contain 2 compute fields: Type and Value
   - Type_Data_1 = "Data-1"
   - Value_Data_1 = Data_1
   - same goes for ceData2 and ceData3

- In appendAllData, select "Allow disjoint schema"

- ceData also contain compute field Type and Value, and using Case in the formula

for Type_Data
case
  when Type_Data_1 is not null then "Data-1" 
  when Type_Data_2 is not null then "Data-2"
  when Type_Data_3 is not null then "Data-3"
end

for Value_Data
case
  when Type_Data_1 is not null then Data_1
  when Type_Data_2 is not null then Data_2
  when Type_Data_3 is not null then Data_3
end


Here our result:

Salesforce: Query Multi-Currency Field

When you have multi-currency enabled in your Salesforce org., you will see the converted value of currency fields in the page layout, list view, and report.

Creating Record

When creating a new record, the default selected currency is User currency, instead of Corporate currency.

Viewing Record

• If User currency equal to record currency --> no amount conversion 
• If User currency different from record currency --> record currency appear at front, continue with user current and the conversion amount under brackets. This has nothing related to corporate currency.

Page Layout

My user currency is USD and the corporate currency is USD too.

If I change my currency to SGD

List View

The same goes for ListView, if the record currency is different from your user currency, the list view will show the amount in the opportunity currency and the value in your currency in brackets.

Report

Currency fields in reports are shown in their original currencies. Salesforce gives the option to get the values in the Opportunity record currency and in the converted amount -- by default, when creating a report, the converted currency will be in the user personal currency, but the user will be able to change to any active currency.

SOQL

When you query Salesforce currency fields, SOQL will always return currency values as defined in the CurrencyIsoCode.

Format() and convertCurrency()

We can use Format() and convertCurrency() functions for currency field in SOQL:
- Use FORMAT with the SELECT clause to apply localized formatting to standard and custom number, date, time, and currency fields, the format applied these fields reflect the appropriate format for the given user locale.
- Use convertCurrency() in the SELECT clause to convert currency fields to the user’s currency.

Looks at this sample: SELECT Id, CurrencyIsoCode, Amount, convertCurrency(Amount) UserAmount, FORMAT(amount) TextAmount, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity order by currencyisoCode

You can’t use the convertCurrency() function in a WHERE clause. If you do, an error is returned, but you can use the following syntax to convert a numeric value to the user’s currency from any active currency in your org. WHERE Object_name Operator ISO_CODEvalue

e.g. SELECT Id, Name FROM Opportunity WHERE Amount > USD5000
In this example, opportunity records are returned if the record’s currency Amount value is greater than the equivalent of USD5000. For example, an opportunity with an amount of USD5001 is returned, but not JPY7000.

How to get currency value in corporate currency using SOQL?
In Spring ’18 release, Salesforce introduces new formula functions ADDMONTHS, CURRENCYRATE, MCEILING, MFLOOR and WEEKDAY. So this purpose, we can make use of CurrencyRate() function.

CURRENCYRATE returns the conversion rate to the corporate currency for the given currency ISO code. If the currency is invalid, returns 1.0.

We can create a simple formula field
Amount / CURRENCYRATE(TEXT(CurrencyIsoCode))

Here is the result:

If you implement dated exchange rate, CurrencyRate() function do not support it yet, it will always use the standard exchange rate, unless you always keep the conversion rate aligned.

Reference:

• Formula Operators and Functions
• Querying Currency Fields in Multi-Currency Orgs
• Format()
• New Formula Functions

Einstein Analytics: Result Binding

There are 2 types of binding in Einstein Analytics:
1. Selection: the query result based on user selection
2. Result: the query result based on the changes in other steps

We have discussed selection binding in the previous blogs: here, here, and here. Now let us looks at result binding.

Here is the sample for this blog:
- We have 2 datasets which are not really linked, but both have user Id and Fiscal
- Opportunity Split dataset have Close Date, which can be used as a toggle

Steps:
1. Create Static Step to have Previous Quarter, Current Quarter and Next Quarter
                            "Static_Period_1": {
                "broadcastFacet": true,
                "label": "Static_Period",
                "selectMode": "singlerequired",
                "start": {
                    "display": [
                        "Current Quarter"
                    ]
                },
                "type": "staticflex",
                "values": [
                    {
                        "display": "Previous Quarter",
                        "value": -1
                    },
                    {
                        "display": "Current Quarter",
                        "value": 0
                    },
                    {
                        "display": "Next Quarter",
                        "value": 1
                    }
                ]
            }

2. For wizard for Opportunity Split dataset, add selection binding to filter the result based on the toggle.
           "query": {
                    "values": [
                        "Fiscal",
                        "Id",
                        "User_Id",
                        "Close_Date",
                        "Amount"
                    ],
                    "filters": [
                        [
                            "Close Date",
                            [
                                [
                                    [
                                        "fiscal_quarter",
                                        "{{cell(Static_Period_1.selection,0,\"value\").asString()}}"
                                    ],
                                    [
                                        "fiscal_quarter",
                                        "{{cell(Static_Period_1.selection,0,\"value\").asString()}}"
                                    ]
                                ]
                            ],
                            ">=<="
                        ]
                    ],
                    "order": [
                        [
                            "Fiscal",
                            {
                                "ascending": true
                            }
                        ]
                    ]
                }

* Static_Period_1 is the step name for static step, see (1)

3. For wizard for Forecast Quota dataset, add result binding to filter the result based on the column in the Opportunity Split step.
          "query": {
                    "values": [
                        "Fiscal",
                        "User_Id",
                        "Quota"
                    ],
                    "filters": [
                        [
                            "Fiscal",
                            [
                                "{{cell(lens_1.result,0,\"Fiscal\").asString()}}"
                            ],
                            "in"
                        ]
                    ],
                    "order": [
                        [
                            "Fiscal",
                            {
                                "ascending": true
                            }
                        ]
                    ]
                }

* Lens_1 is the step name contains selection binding, see (2)
* Fiscal is the column name in Lens_1 step


For this blog sample, when user selects "Next Quarter" in toggle, the Opportunity Split data will be filtered based on selection defined in static step, then Forecast Quota data will be filtered based on Fiscal changed in Opportunity Split step.

Here is the complete JSON file.



Reference: Result Binding

Salesforce: Forecast with Opportunity Split

This is the continuation of previous blog Setup Forecast Quota, in this blog, we will share about Forecast with Opportunity Split Overlay.

1. Opportunity Team and Opportunity Splits

Here is the scenario:
Two users, both users have been enabled for Forecast
1. Johan Forecast is the manager
2. George Mann report to Johan Forecast

Two closed-won opportunities with Overlay split:

opportunity #1

opportunity #2

2. Forecast
Let us the result see in the Forecast tab:

Highlight legend:
- Yellow: this is quota as we discussed in the previous blog
- Pink: this is the forecast item for Closed forecast
- Green: this is summary from all subordinates, including the manager
- Blue: USD 305,500.00 comes from opportunity #2 which is the total amount for the 2nd and 3rd; USD 75,000.00 is from opportunity #1. 


3. SOQL
SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db2v000005aTHcCAM'

Summary:

• The Forecast Amount in ForecastingItem is auto-populated from Opportunity Split Amount based on the Forecast Type and Forecast Category.
• The Period Id in ForecastingItem is auto-populated based on Opportunity Closed Date.
• Each user will only have 1 line for ForecastingItem in a period for a Forecast Type.
• If you have multi-currencies enabled, only 1 currency will be returned in the query result, this currency is based on defined Corporate Currency.
• The Forecast Amount from ForecastingItem in SOQL is roll-up to the manager, and managers numbers will roll-up to the managers' manager.

Forecast Manager in Forecast Hierarchy

Sample:

There are 2 users under Staff 1: Song Lee & Free Man

Forecast tab

SOQL
SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db0k000000076kCAA' ORDER BY ForecastAmount

What will happen if we remove Maria Ann as Forecast Manager for the role hierarchy General Manager?

Forecast tab

Notice:
1. The Amount is no longer rollup from Maria Ann subordinates'. Forecast managers see forecast rollups from users below them in the forecast hierarchy.
2. Click the arrow next to label 'Maria Ann' will not drill down to the subordinates level.
3. Jack Bob numbers are only from Linda Yie as Maria Ann not contributing anything.
4. The number from Song Lee and Free Man are not roll-up to anyone.

SOQL

SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db0k000000076kCAA' ORDER BY ForecastAmount

If Maria Ann have her owned Opportunity Splits

Same for SOQL result, Maria Ann will have USD 18,000.00 for the Forecast Amount.


Now, let us remove Allow Forecasting from Maria Ann

Maria Ann does not appear in the Forecast tab at all.

SOQL:

Remember that Maria Ann still owned Opportunity Split, but she does not appear in both Forecast and SOQL.


Summary:
1. Allow Forecasting is a must for all forecast users.
2. Each manager role in the forecast hierarchy should have a user assigned as the Forecast Manager.
3. The API name for "Allow Forecasting" is ForecastEnabled, you can mass update it with API.

Salesforce: Setup Forecast Quota

1. Enable Opportunity Team

  

2. Enable Opportunity Split and Add Opportunity Split Type (optional)

For this blog, I am adding Overlay opportunity split type

 3. Enable Forecast, Add Forecast Type (optional), and Enable Quota

enable forecast

adding forecast type 

You may need to refresh your web browser to see the “Forecasts Quotas” tab to appear.

4. Enable user for “Allow Forecasting”

From the user detail page or Forecast Hierarchy.

user detail

forecast hierarchy

  

 5. Assign Quotas to Users

Make sure you select the right Forecast Type and select the right currency (if necessary)

Or use the Data Loader tool to load CSV into Forecasting Quota object

6. Set Forecast Manager for each Role Hierarchy

This is only necessary if user have subordinates as per role hierarchy, set up this from Forecast hierarchy.

7. Verify Quota

7A. Forecast Tab

Make sure to select correct Period, Forecast Type, and Currency

7B. SOQL

As mentioned in (5), we can use Data loader to load quota to Forecast Quota object. So, the same we can query from Forecast Quota object too.

SELECT Id, QuotaOwnerId, QuotaAmount, QuotaOwner.Name, ForecastingTypeId, PeriodId FROM ForecastingQuota

• From the above query, this shows us that Quota Amount numbers are NOT roll-up from subordinates to the manager.
• The currency here is based on currency defined when added the quota for each users.

Reference:

• Enable Quotas in Collaborative Forecasts
• Manage Quota Data for Collaborative Forecasts via Data Loader or the API