For example, min, max, first, last, average and stddev are available to help slice and dice your data. The full list of aggregate functions is available in the help documents.
The trick to access these other options in the user interface is scrolling down in the Measure selection box. Once you scroll, a world of other features become available.
As a bonus, a binding statement will enable switching the chart between one and two standard deviations. All of this can be done without writing a single SAQL statement.
Building The Chart
Our first step is to create a lens, leveraging the compare table. We will use a simple dataset based on Opportunities augmented with Account details.
Start by selecting a Bar grouping of Industry or another field of interest on the Opportunity, such as Owner. Then, add the Average sales Amount measure as the Bar Length.
Start by selecting a Bar grouping of Industry or another field of interest on the Opportunity, such as Owner. Then, add the Average sales Amount measure as the Bar Length.
Next, add a second column for the standard deviation of sales Amount, using Stddev.
Now it's time to add a couple of formula columns. The first column will calculate the value for one standard deviation below the average. For both of these formulas, we can switch to a compare table, or use the + Add Formula shortcut when working with measures.
This is a simple A-B formula to subtract the standard deviation from the Average.
The second formula column will calculate the value for one standard deviation above the average. In this case, A + B produces the desired result.
Create two more formula columns that provide two sigma below and above the average.
Here is the completed compare table with six columns, shown as a chart.
To wrap up the lens development, we need to change a few chart properties.
- Hide the Std Dev of Amount column using the drop down next to its name.
- In Chart Properties:
- Change from a bar chart to a Combo chart in order to have bars and lines.
- Change the Axis mode to Single Axis.
- Uncheck Show values in chart lines.
- In Y-Axis (Left), hide the Y axis title
- Under Legend, move the legend to bottom-center.
Building the Dashboard
With the lens ready, use the clip option to add this to a new dashboard. With the step on the dashboard, let's add the ability to toggle between one and two standard deviations.
A toggle lets the user easily change from on option to another. As an alternative option, a List widget could also be used. For the toggle dataset, we will use a static step with options for 1 standard deviation (or sigma) or 2 standard deviation. We'll come back to adjust the values in a little bit.
Now to toggle between two sets of measures, we need to dive into the dashboard JSON. To access the JSON, use command-E (Mac) or ctrl-E (Windows). If you look at the chart step, it is in a compare table format. The structure of the query node leads us to this conclusion.
A toggle lets the user easily change from on option to another. As an alternative option, a List widget could also be used. For the toggle dataset, we will use a static step with options for 1 standard deviation (or sigma) or 2 standard deviation. We'll come back to adjust the values in a little bit.
Now to toggle between two sets of measures, we need to dive into the dashboard JSON. To access the JSON, use command-E (Mac) or ctrl-E (Windows). If you look at the chart step, it is in a compare table format. The structure of the query node leads us to this conclusion.
"query": {
"measures": [
[
"avg",
"Amount",
"A"
],
[
"stddev",
"Amount",
"B"
],
[
"count",
"*",
"C"
],
[
"count",
"*",
"D"
],
[
"count",
"*",
"E"
],
[
"count",
"*",
"F"
]
],
"columns": [
{
"query": {
"measures": [
[
"avg",
"Amount"
]
],
"groups": [
"Account.Industry"
]
}
},
{
"query": {
"measures": [
[
"stddev",
"Amount"
]
],
"groups": [
"Account.Industry"
]
}
},
{
"query": {
"measures": [
[
"count",
"*"
]
],
"groups": [
"Account.Industry"
],
"formula": "A-B"
},
"format": "currencydollars",
"header": "Lower Sigma"
},
{
"query": {
"measures": [
[
"count",
"*"
]
],
"groups": [
"Account.Industry"
],
"formula": "A+B"
},
"format": "currencydollars",
"header": "Upper Sigma"
},
{
"query": {
"measures": [
[
"count",
"*"
]
],
"groups": [
"Account.Industry"
],
"formula": "A - (2 * B)"
},
"format": "currencydollars",
"header": "Lower 2 Sigma"
},
{
"query": {
"measures": [
[
"count",
"*"
]
],
"groups": [
"Account.Industry"
],
"formula": "A + (2 * B)"
},
"format": "currencydollars",
"header": "Upper 2 Sigma"
}
],
"groups": [
"Account.Industry"
]
}
One of the other concepts that comes into play when toggling measures, is the columnMap. It's used to map columns in the step to how the display is rendered in the widget. While we can use bindings to change the column used in a chart, columnMap doesn't currently support bindings.
A common way to resolve this issue is to simply delete the columnMap from the widget. While this "works", it can produce some odd behavior - such as how columns are shown/hidden when exploring from the dashboard.
An alternate approach is to creatively use the compare table to swap a formula, without changing the columns. We could recreate the entire formula in the binding statements, or use another trick.
Column Swapping Trick
First, start by editing the step on the dashboard. Then add two more formula columns. The first simply references the Lower Sigma column and the second references the Upper Sigma column.
Next, hide the four columns that had lower and upper 1 and 2 sigma values. This will leave you with three metrics in the chart.
Dashboard Bindings
Now, on to the static step and formula bindings. For the static step, let's adjust the JSON.We are going to add two additional attributes to hold the lower and upper columns to use. For the 1 sigma option, we will use columns C and D and E and F for 2 sigma.
It's also be a good time to change the step name, to make it easier to remember in the binding statement. Use command-F (Mac) twice or ctrl-F (Windows) twice to bring up find and replace in the JSON.
"Static_Deviation": {
"broadcastFacet": true,
"label": "Deviation",
"selectMode": "single",
"type": "staticflex",
"values": [
{
"display": "1 Sigma",
"value": "1S",
"lower": "C",
"upper" : "D"
},
{
"display": "2 Sigma",
"value": "2S",
"lower": "E",
"upper" : "F"
}
]
}
Finally, it is time to add the binding statement to the chart. We simply need to change the formula for the last two columns in compare table to toggle between different values. The binding statement will use the static selector to determine which option to display, based on the selected option.
Replace:
"formula" : "C"
With:
"formula": "{{cell(Static_Deviation.selection, 0, \"lower\").asObject()}}"
Replace:
"formula" : "D"
With:
"formula": "{{cell(Static_Deviation.selection, 0, \"upper\").asObject()}}"
Don't be alarmed if the dashboard doesn't work properly. The binding statement relies upon a selection in the static step. The step we added doesn't have a selection yet. If you view the dashboard and pick an option, the chart will update.
We have two options to fix this issue. We can use a coalesce statement in the binding to handle no selection. An easier option is to change the Step Selection Type to Single Selection (required). This will automatically select the first option.
Alternatively, we could use Pick Initial Values. Although, a user could unselect any option and break the chart.
Now, view the dashboard and toggle between viewing 1 and 2 standard deviations from the average.
1 Sigma
2 Sigma
Bonus Thoughts
With something like Opportunity Amount, a negative value doesn't make a lot sense. At worst, we don't do the deal, which means the Amount would be 0. We can adjust the formula to return 0 if the value is negative. Simply change the formula to the following for the lower amount:
case when A-B < 0 then 0 else A - B end
Unfortunately, once you bind the step you can no longer edit it without going to JSON. Try to make the changes before binding. If you get stuck, simply Explore the chart and interact with it in a lens.
Here's our final dashboard.
No comments:
Post a Comment