Pages

Sunday, July 14, 2019

Einstein Analytics: Using XMD (Extended Metadata) for Percentage

Getting Started
When we configure Einstein Analytics Dataset, we not realize have been playing around with XMD (Extended Metadata).

When you explore dataset to a lens, and click "Fields" for:
- Rename
- Edit Values
- Number Format
Save the changes will update Dataset XMD.

Let us load a CSV file

Here is the blank structure of XMD file:
{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [],
  "dimensions": [],
  "measures": [],
  "organizations": [],
  "showDetailsDefaultFields": []
}


Let us create a dashboard based on that CSV file



Now, let us do "Fields" edit from lens with Rename, Edit Values, and Number Format. Here is the updated XMD file
{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [],
  "dimensions": [
    {
      "conditionalFormatting": {},
      "customActions": [],
      "field": "Column1",
      "label": "Name",
      "members": [
        {
          "label": "John",
          "member": "Lee"
        }
      ],
      "recordDisplayFields": [],
      "salesforceActions": []
    }
  ],
  "measures": [
    {
      "conditionalFormatting": {},
      "field": "Data_1",
      "format": {
        "customFormat": "[\"0.00%\",100]"
      }
    },
    {
      "conditionalFormatting": {},
      "field": "Data_2",
      "format": {
        "customFormat": "[\"#,###\",1]"
      }
    }
  ],
  "organizations": [],
  "showDetailsDefaultFields": []
}

Highlight notes:
- Yellow = rename
- Green = edit value
- Aqua = number format as Percent
- Fuchsia = number format as Number


Now, open back the dashboard (you need to close it first if the dashboard still open).




Usage in SAQL field
Let us do a simple SAQL to the widget to produce total Data-1 + Data-2 and Data-1 / Data-2.

q = load "percent_data";
q = group q by 'Column1';
q = foreach q generate 'Column1' as 'Column1', sum('Data_1') as 'sum_Data_1', sum('Data_2') as 'sum_Data_2';
q = foreach q generate 'Column1' as 'Column1', 'sum_Data_1' + 'sum_Data_2' as 'Total', 'sum_Data_1' / 'sum_Data_2' as 'Result_1';
q = order q by 'Column1' asc;

* notice that we still use Column1 as this is referred to the field API name

The SAQL produce two new fields which do not exist in the dataset: Total and Result_1.



However, we need to show them in the percentage format, so we need to add in the XMD file, because this is a measure field and it does not exist in the dataset, we should add it into derivedMeasures.

{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [
    {
      "conditionalFormatting": {},
      "field": "Total",
      "format": {
        "customFormat": "[\"#.##%\",1]"
      },
      "label": "Total",
      "showInExplorer": false
    },
    {
      "conditionalFormatting": {},
      "field": "Result_1",
      "format": {
        "customFormat": "[\"#.##%\",1]"
      },
      "label": "Result",
      "showInExplorer": true
    }  
  ],
  "dimensions": [
    {
      "conditionalFormatting": {},
      "customActions": [],
      "field": "Column1",
      "label": "Name",
      "members": [
        {
          "label": "John",
          "member": "Lee"
        }
      ],
      "recordDisplayFields": [],
      "salesforceActions": []
    }
  ],
  "measures": [
    {
      "conditionalFormatting": {},
      "field": "Data_1",
      "format": {
        "customFormat": "[\"0.00%\",100]"
      }
    },
    {
      "conditionalFormatting": {},
      "field": "Data_2",
      "format": {
        "customFormat": "[\"#,###\",1]"
      }
    }
  ],
  "organizations": [],
  "showDetailsDefaultFields": []
}

Notes:
1. add those field under derivedMeasures
2. field name can be different with label
3. you can set the field visibility in explorer


Here is the end result:



When you browse the dataset into a lens and open the Fields



JSON Online Editor
When you download XMD JSON file from Einstein Analytics and open in Notepad++, it would be show as one row only, where it is impossible for you to read.edit it. You can use JSON Online Editor to show as tree format, by copy and paste the JSON text into the left text area, click the right arrow to show as tree format.

Then to get human eye friendly JSON format, click left arrow and the text at the right will reformat.




Reference:


Wednesday, July 10, 2019

Einstein Analytics: Default Widget Style and Transparent Widget

Default Widget Style
When you drag a new widget into dashboard designer, in many times we need to change the widget style, from: background color, border, border color, border width, border radius. You also need to make sure to have a consistent style across the widgets. It is not an issue if you have only a few widgets, but when you have more and more widgets, this manual setting takes time and you may choose the wrong color or style.

Make use of Default style
When you expand Widget Style property, the 1st option and it is ticked by default is "Use defaults".


When this option is selected, all properties defined for Widget Style will be ignored.

Defined Default Widget Style
From Dashboard Properties (click  ... icon at top right)



Then you will found "Widget Default Properties", so configure the background color, border, border color, border width, border radius here.



So when you drag a new widget, the style will be applied, if not, make sure "Use default" is selected. If you change the default style from dashboard properties, all the existing widget will be adjusted to the new style.


Layout Properties
Just to make it clear, dashboard properties is not the same with layout properties, even both applied to the whole pages (if you have multiple pages in the dashboard). You can set layout properties by clicking gear icon with layout name (by default is called "Default"), at the top left of dashboard designer.


In layout properties, you can define:
- Name
- Number of Columns
- Row Height
- Cell Spacing: Horizontal and Vertical
- Max Dashboard Width
- Background Color: Grid and Gutter


Transparent Widget 
Transparent widget means, no background color for the widget, it will follow layout properties grid color. You can define the transparent background color as default widget style or to for independent widget.

For default widget style, from dashboard properties, click the background color, then select Custom scroll the bar to the left



You can implement the same for an individual widget, just make sure "Use defaults" is not selected. You can compare the following 2 widgets below, the upper one with transparent background, and the lower one with white background.





Monday, July 1, 2019

Salesforce: ForecastingQuota and ForecastingItem object

ForecastingQuota, this object stores an individual user’s or territory’s quota for a specified time period. The “Manage Quotas” user permission is required for creating, updating, or deleting quotas.  The “View All Forecasts” permission is required to View any user's forecast, regardless of the forecast hierarchy. Available in API versions 25 and greater. Object Id prefix is 0J9.

Once the quota added from the setup menu or Data Loader, it will be stored in this object and you can query it.



Query ForecastingQuota
SELECT Id, ForecastingTypeId, QuotaAmount, QuotaOwnerId, QuotaOwner.Name, StartDate FROM ForecastingQuota ORDER BY StartDate DESC



ForecastingItem, this is a read-only object used for individual forecast amounts. Users see amounts based on their perspectives and forecast roles. Available in API versions 26 and greater. Object Id prefix is 0G3.

The amounts users see include one of the following when forecasting in revenue: AmountWithoutAdjustments, AmountWithoutManagerAdjustment, ForecastAmount, OwnerOnlyAmount.

The amounts users see include one of the following when forecasting in quantity: QuantityWithoutAdjustments, QuantityWithoutManagerAdjustment, ForecastQuantity, OwnerOnlyQuantity.

Additionally, note that users:
- with the “View All Forecasts” permission have access to all ForecastingItem fields.
- without the “View All Forecasts” permission have access to all fields for their own subordinates and child territories.
Other users can see the ForecastingItem object, but not its records.

Let us see a sample of how the data is stored
SELECT Id, OwnerId, Owner.Name, ForecastAmount, ForecastCategoryName, ForecastingTypeId, PeriodId FROM ForecastingItem WHERE Period.StartDate = 2019-07-01 ORDER BY ForecastingTypeId, Owner.Name, ForecastCategoryName

** unfortunately we cannot do relationship query for ForecastingType

figure 1

Notes from the above query and the result:
- ForecastCategoryName is Forecast Category which is mapped to Stage
- There is no forecast period in this object, but PeriodId, so we can use it to determine Forecast period
- ForecastingTypeId is referred to Forecast Type configured

Query ForecastingType
SELECT Id, DateType, DeveloperName, MasterLabel, RoleType FROM ForecastingType WHERE IsActive = true

soql result from ForecastingType


Now let us see the data by running an Opportunity report:

figure 2


Then let us also see the Forecast result:

figure 3

Now, let us compare how is the data stored in ForecastingItem object, remember this object is read-only, so we can't update it manually.

  • Figure 1; row 1; Charlie User C in Jul 2019; for Forecast Category = Pipeline with Forecast Amount = $55,000 -- if you see figure 3, it is $0 for Charlie User C, but since Dave User D is reported to Charlie in the role hierarchy, so amount from Dave roll-up to Charlie
  • Figure 1; row 2; Charlie User C in Jul 2019; for Forecast Category = Closed with Forecast Amount = $295,000 -- look at figure 3, this amount is sum of $120,000 (Dave's amount) + $139,500 (Charlie's amount). If we further drill Charlie's amount of $139,500 -- look at figure 2, it is the sum of row 1 ($50,000) and row 2 ($89,500)
  • Figure 1; row 3; Dave User D in Jul 2019; for Forecast Category = Pipeline with Forecast Amount =$55,000 -- as no one report to Dave in the role hierarchy, this number is only his opportunity, drill down to figure 2, it is row 3 ($15,000) + row 4 ($40,000) 
  • Figure 1; row 3; this is simply Dave User D amount for Forecast Category = Closed
  • Figure 2; row 6; this is not calculated into ForecastingItem, because forecasting setting in this example in monthly, while row 6 closed date is Aug 2019.

In this sample, we are using monthly forecast setting, Salesforce also supports option to use Quarter forecast period too.


Reference:


Page-level ad