Bindings in Compare Table
Published in
9 min readApr 29, 2019
We saw how Pivot Tables work and when to use them vs. Compare Table. In this blog, we will see how to make Compare Tables more flexible using bindings. I recently had a used case where the requirement was to give a client the ability to visualize a chart/graph in a particular unit of measure while the rest of the clients can choose from what they want. Of course, we can do this using SAQL. But I didn’t want to use SAQL because at times just reading through a compact form makes it easier than reading a SAQL.
Problem Statement
We spoke about Chicago Taxi Data in the blog about Pivot Tables. Using the same dataset, let’s say, we want to restrict that for a particular Taxi Company (Chicago has many), the Total Fare should be just the Fare and for everyone else, the total fare can be the sum of the Fare + Extras/Tips/Tolls. We can use a case statement but then we want to give the ability to choose what the user wants to add to the Fare. And to do that we need to use the Static Step and Binding in the Compare Table.
Solution
To use bindings, we will use a dashboard this time instead of a simple lens. So, let’s start by creating a dashboard. I changed the dashboard layout to 48 columns with 4 X 4 cell spacing (personal preference). You can choose whatever you like and color as you like as well.
- Click Create a Step and choose the dataset. We choose Taxi Data (Taxi_Trips) here as mentioned above. Name the lens (Each non-static step in a dashboard can be viewed as a lens within the dashboard.) Click on Table Mode -> Compare Table. All it shows currently is the total number of rows in the dataset.
- Let’s group by the Company and Use the first column as “Sum of Fare”, then clone this column twice.
- We will use the first column as is, the second one will be our binding and the last one will be the result of binding and the first column.
- Click Done and go back to the dashboard.
- Now create a static step to choose between Extras, Tips, and Tolls.
- To do this again click Create Step -> Create a Static Step with Custom Values. That opens the panel below which I have filled up with some values (to be changed in the Dashboard JSON). We will call the Step Select_Addt_Fare (Display Label).
- Type Cmd + E (Mac) or Ctrl + E (Windows) to go back and forth from the Dashboard to the JSON. This command also works in the standalone lens if you want to change something there. In this case, though the display names and values are the same we still need to change them in the JSON because they are currently just strings and we want to use them as measures. Another reason you want to use JSON is say, we want to use a different field instead of Extras. It is easier to just change it in the JSON rather than creating a new static step! The “value” in the step snippet below is the placeholder where we can change the value.
"Select_Addt_Fare_1": {
"type": "staticflex",
"numbers": [],
"strings": [],
"groups": [],
"broadcastFacet": true,
"selectMode": "single",
"label": "Select_Addt_Fare",
"values": [
{
"display": "Extras",
"value": "Extras"
},
{
"display": "Tips",
"value": "Tips"
},
{
"display": "Tolls",
"value": "Tolls"
}
]
}- Let’s add the measures to the “value” which we will use as part of the binding. When we see the JSON for the Bindings lens, we will see that it has measure with a specific format. To make the binding valid, we have to replicate the same. The first code snippet shows what the measure is in the Bindings_1 lens. And we will use the same but change the field/column in the static step values.
[
"sum",
"Fare"
]- After changing these the Static step looks like:
"Select_Addt_Fare_1": {
"broadcastFacet": true,
"label": "Select_Addt_Fare",
"selectMode": "singlerequired",
"start": {
"display": [
"Extras"
]
},
"type": "staticflex",
"values": [
{
"display": "Extras",
"value": [
"sum",
"Extras"
]
},
{
"display": "Tips",
"value": [
"sum",
"Tips"
]
},
{
"display": "Tolls",
"value": [
"sum",
"Tolls"
]
}
]
}- We will now add the Static Step (Step Name: Select_Addt_Fare_1) to the dashboard. We can do this in 2 ways: Either as a list selector or a toggle. If doing a list selector, we have to ideally give it a name. Toggle doesn’t need a name. The image below shows both. But we will use the Toggle in this exercise. There is no difference between the 2 other than how they show on the dashboard. For list selector, pull the list selector to the dashboard and add the field. For the toggle, pull the toggle selector to the dashboard and add the field. We can either leave them as is or make the selection required. In this case, we will choose “Single Selection (Required)” from Step Properties. By default, it takes the first value. We can go to the Pick Initial Selections from the dropdown on the dashboard (top-right corner ellipsis) and choose which one we want to be the default.
- When we go to the JSON for the Select_Addt_Fare_1 Step, we can find a small snippet of code added to the initial JSON when we created the static step which reads which is the default field.
"Select_Addt_Fare_1": {
"broadcastFacet": true,
"label": "Select_Addt_Fare",
"selectMode": "singlerequired",
"start": {
"display": [
"Extras"
]
},
"type": "staticflex",
"values": [
{
"display": "Extras",
"value": [
"sum",
"Extras"
]
},
{
"display": "Tips",
"value": [
"sum",
"Tips"
]
},
{
"display": "Tolls",
"value": [
"sum",
"Tolls"
]
}
]
}- We will now use this static step to create the binding in the compare table. Before that, we will give our columns Column Alias and Column Header. So, we re-open the Bindings (Bindings_1) lens. Click on the measure column -> Edit This Column and provide names to the 3 measures.
- We will call the first column Fares, second Bind, and the last Total.
- The reason, we should always label the columns is, it becomes easier to find them in the JSON and we don’t have to deal with alphabets (the default Column Alias). This saves a lot of time when we have to deal with too many columns and calculation fields.
- Update the lens and add it to the dashboard.
- We now go to the JSON. So Cmd/Ctrl + E (as the case may be). Find Bindings_1. Under this step, we find the following parameters:
- type: Compare Tables are aggregateflex
- query: This is further divided and is where we change the column to bindings and any other change like adding groups etc. we want to make.
- visualizationParameters: What we want to see in the dashboard. Say, we have 10 columns and we want to see only 5, this is where we do those changes.
- datasets: The dataset used in this lens
- useGlobal: Indicates whether to apply global filters to this step
- isGlobal: This was used for dashboards created before Winter ’18. So not required in our case.
- label: Name of the lens
- broadcastFacet: Allow updates in other lenses in the dashboard based on the selection in this lens including list selectors/date selectors/range etc.
- receiveFacet: Receive updates in other lenses in the dashboard based on a selection in this lens including list selectors/date selectors/range etc.
- selectMode: How many rows we can select in the table, 1 or more than 1.
- Back to Query. Under query, we have :
- measures: The fields that we added in the measures part of the lens
- columns: Any change like adding a formula, binding, etc. is done here.
- groups: Ultimately, all the dimension/date fields the columns should be grouped by.
- Under columns, find Bind, our second column where the binding will be added. This is what the JSON currently looks like. We will change the highlighted portion under measure because we want to give the user the flexibility to choose between Extras, Tips, and Tolls.
{
"query": {
"measures": [
[
"sum",
"Fare"
]
],
"groups": [
"Company"
]
},
"header": "Bind",
"name": "Bind"
}- When we add the bindings, the measure is already replicated in the Static Step. So, we remove everything within the first square bracket and instead add the following:
"{{cell(Select_Addt_Fare_1.selection, 0, \"value\").asObject()}}"- Next, we make the last change in the lens which is to choose between using the sum of the fares and binding column or just the binding column. Let’s say, any company that has the word Chicago in it is not interested in the extras, tips, and tolls because they don’t get it (I am totally making an assumption). For others, it will be the sum of the Fare and Extras, Tips or Tolls. So, all we do is add a formula field to the last column (Total). Once a formula is added, the dashboard uses that irrespective of what the measure says. The complete query for Bindings_1 step now looks like:
"query": {
"measures": [
[
"sum",
"Fare",
"Fares"
],
[
"sum",
"Fare",
"Bind"
],
[
"sum",
"Fare",
"Total"
]
],
"columns": [
{
"query": {
"measures": [
[
"sum",
"Fare"
]
],
"groups": [
"Company"
]
},
"name": "Fares",
"header": "Fares"
},
{
"query": {
"measures": [
"{{cell(Select_Addt_Fare_1.selection, 0, \"value\").asObject()}}"
],
"groups": [
"Company"
]
},
"name": "Bind",
"header": "Bind"
},
{
"query": {
"measures": [
[
"sum",
"Fare"
]
],
"groups": [
"Company"
],
"formula": "case when Company matches \"Chicago\" then Fares else Fares + Bind end"
},
"name": "Total",
"header": "Total"
}
],
"groups": [
"Company"
]
},- Ones we close the JSON editor, the dashboard looks like:
- As we can see from the above image, Chicago Medallion Leasing and Chicago Medallion Management have just their fares as Total while for the others, it is the sum of Fares and Binding.
- We may choose to not show the Fares and Bind column to the user at all. And all they want to see is the Total. All we need to do in this case is add the relevant columns to the “column” section of visualizationParameters and columns under parameters of the widget.
"visualizationParameters": {
"options": {},
"parameters": {
"borderColor": "#e0e5ee",
"borderWidth": 1,
"cell": {
"backgroundColor": "#ffffff",
"fontColor": "#16325c",
"fontSize": 12
},
"columnProperties": {},
"columns": [
"Company",
"Total"
],
"customBulkActions": [],
"header": {
"backgroundColor": "#f4f6f9",
"fontColor": "#16325c",
"fontSize": 12
},
"innerMajorBorderColor": "#a8b7c7",
"innerMinorBorderColor": "#e0e5ee",
"maxColumnWidth": 300,
"minColumnWidth": 40,
"mode": "variable",
"numberOfLines": 1,
"pivoted": false,
"showActionMenu": true,
"showRowIndexColumn": false,
"totals": true,
"verticalPadding": 8
},
"type": "table"
}- The widget for Bindings_1 is table_1.
"table_1": {
"parameters": {
"borderColor": "#e1e5ee",
"borderWidth": 1,
"cell": {
"backgroundColor": "#f2f6fa",
"fontColor": "#16325c",
"fontSize": 12
},
"columnProperties": {},
"columns": [
"Company",
"Total"
],
"customBulkActions": [],
"evenRowColor": "#f2f6fa",
"exploreLink": true,
"header": {
"backgroundColor": "#e4ecf2",
"fontColor": "#5c7a99",
"fontSize": 12
},
"innerMajorBorderColor": "#a9b7c7",
"innerMinorBorderColor": "#e1e5ee",
"mode": "fittocontainer",
"numberOfLines": 1,
"oddRowColor": "#ffffff",
"pivoted": false,
"showActionMenu": true,
"showRowIndexColumn": false,
"step": "Bindings_1",
"totals": true,
"verticalPadding": 8
},
"type": "table"
},- The final result of the dashboard is:
Few Notes regarding Compare Table
- It is always a good idea/practice to label the columns both Column Alias and Column Header.
- Using a filter in a column (separately) and then adding a formula doesn’t respect the filter! I learned it the hard way.
- Finish as much as you can in the Compare Table before adding any bindings. Once you add a binding, you lose the ability to change it using the UI.
- When adding a group to a Compare Table in JSON, always add the groups to all columns and the final grouping. Even if you miss 1, the group will not show up on the dashboard.
More References
- To learn more about bindings, refer Rikke Hovgaard blog on the same.
- And of course Peter Lyons YouTube channel
