Introduction
The commenting outlines feature of a SAQL query is a common method used to debug or run chunks of your code to validate efficacy. But what if SAQL comments could allow you to do more than just troubleshoot? In this blog, we’ll explore a unique method of displaying different KPIs (and different numbers of KPIs) on a single chart, using the SAQL comments feature to drive the chart trends.
The Problem Statement
Clients often ask for a single trend chart with a menu that allows users to toggle with the metric or KPI that is trending. Additionally, the list of KPIs may differ across currency, percentage, or other number types and you may be asked to add year-over-year trend lines. Many developers resort to using multiple charts & pages but we’ll explore a solution that can accomplish this in one page, preserve number types, and display any number of KPIs driven by users selection.
Building the Solution
For this demo we’re utilizing a dataset that includes the following KPIs by month:
- Total Sales
- Returning Subscribers
- New Subscribers
- Forecast
- Target Sales
Our requirements are toggled between 3 options:
- Total Sales – as a bar with a year-over-year trend line on top
- Returning vs. New Subscribers – represented as a stacked bar
- Forecast vs. Target – with the forecast as a bar and target as a line
Before diving into the solution, let’s see the end result:
Creating the Chart
Let’s begin by creating a timeline combo chart on your dataset:
- Insert your desired date grouping in the “Time Axis” section
- Place as many of your KPIs available in your dataset on the “Y-Axis” shelf
- Now switch the chart axis to “Dual Axis” to bring all your measures together
SAQL Adjustments
Toggle into the SAQL editor where you’ll make a few changes and your SAQL query should model the below format:
- Format the code so your projected fields are all represented on their own lines
- Manually combine the date periods (if using more than one date) to create a unified, single date field (Refer to line 4)
- Create any calculations, such as YoY that you want to be displayed on any selections (Refer to line 9)
- Alias your measures to reflect the name that you want to be displayed on the chart
- Ensure the closing semicolon on your foreach projection is on its own line (Refer to line 11)
q = load “Blog_Dataset”;
q = group q by (‘Date_Year’, ‘Date_Month’);
q = foreach q generate
‘Date_Year’ + “~~~” + ‘Date_Month’ as ‘Date_Year~~~Date_Month’
,sum(q. ‘Total_Sales’) as ‘Total Sales’
,sum(q. ‘New_Subscriptions’) as ‘New Subs’
,sum(q. ‘Returning_Subscriptions’) as ‘Returning Subs’
,sum(q. ‘Forecast’) as ‘Forecast’
,(sum(q. ‘Total_Sales’)/sum(q. ‘Previous_Total_Sales’))-1 as ‘YoY’
,sum(q. ‘Target’) as ‘Target’
;
q = order q by (‘Date_Year~~~Date_Month’ asc);
q = limit q 2000;
Run the SAQL query and switch back to the chart editor. You should now be able to see your measures with their proper alias’ displayed on the Y-Axis shelf.
- Use the chart formatting “Series” option to assign the visualization type to each measure (Bar, Line, etc)
- Check the box in the formatting window to “Stack Bars”
- Assign number formatting to each measure (Currency, Percentage, Number, etc)
- Assign desired custom colors to each measure using conditional formatting
Click on Done and exit the chart editor. The next step is to open the advanced editor on your chart and delete the ColumnMap section of the JSON. This will allow the dynamic binding to function.
q = load “Blog_Dataset”;
q = group q by (‘Date_Year’, ‘Date_Month’);
q = foreach q generate
‘Date_Year’ + “~~~” + ‘Date_Month’ as ‘Date_Year~~~Date_Month’
,sum(q. ‘Total_Sales’) as ‘Total Sales’
,sum(q. ‘New_Subscriptions’) as ‘New Subs’
,sum(q. ‘Returning_Subscriptions’) as ‘Returning Subs’
,sum(q. ‘Forecast’) as ‘Forecast’
,(sum(q. ‘Total_Sales’)/sum(q. ‘Previous_Total_Sales’))-1 as ‘YoY’
,sum(q. ‘Target’) as ‘Target’
;
q = order q by (‘Date_Year~~~Date_Month’ asc);
q = limit q 2000;
Building the Static Toggle SAQL Query
- Drag a list widget onto the dashboard canvas
- Click on it and select the option to create a custom SAQL query
- Add the selection display name as users should see it
- Create a text field for every measure that could show up on your chart
- Name each field so you can identify which measure it relates to (Refer to the example below)
- The final step will be to fill the cell values with the comment notation “–” for measures that are not on the selected measure’s row
- For example, on the “Total Revenue” row, we put “–” in every field except the Total Revenue and Tot Rev YoY fields because when we bind our chart, we want to ensure that Total Revenue and its YoY line are projected from our SAQL query
- Once you have configured your static SAQL query, exit the editor and set the SAQL query to “Single Selection (Required)” so that there will always be one active selection
Binding Your SAQL Query within the SAQL comments feature
The final step is to go back into the advanced editor of your chart and place a selection binding in front of each of your measures corresponding to the cell value of that measure’s dedicated column from your static SAQL query. The final binding result will look similar to this:
q = load “Blog_Dataset”;
q = group q by (‘Date_Year’, ‘Date_Month’);
q = foreach q generate
‘Date_Year’ + ‘Date Month’ as ‘Date Year~~~Date_Month’
{{cell(Static Toggle 1.selection, 0, Total Rev Comment”), asString}}, sum(q. ‘Total Sales’) as ‘Total Sales’
{{cell(Static Toggle 1.selection, 0, “New Subs Comment”).asString}}, sum(q. ‘New Subscriptions’) as ‘New Subs’
{{cell(Static Toggle 1.selection, 0, “Return Subs Comment”).asString}, sum(q. ‘Returning Subscriptions’) as ‘Returning Subs’
{{cell(Static Toggle_1.selection, 0, “Forecast Comment”).asString}}, sum(q. ‘Forecast’) as ‘Forecast’
{{cell(Static Toggle_1.selection, 0, “Rev YoY Comment”).asString())), (sum(q. ‘Total Sales’)/sum(q. ‘Previous Total Sales’))-1 as ‘YoY’
{{cell(Static Toggle 1.selection, 0, “Target Comment”).asString()}}, sum(q. ‘Target’) as ‘Target’
;
q = order q by (‘Date_Year~~~Date_Month’ asc);
q = limit q 2000;
The toggle selections commenting outline within the SAQL comments feature measures the data you didn’t know you could visualize, allowing you to see multiple combo chart types with KPIs across multiple number types.
q = load “0Fb4x000000qXDOCAM/OFc4x00000BRUKUCAT”;
q = filter q by date(‘Date_Year’, ‘Date_Month’, ‘Date_Day’) in [dateRange([2022, 1, 1], [2022, 12, 11)];
q = group q by (‘Date_Year’, ‘Date_Month’);
q = foreach q generate
‘Date Year’ + “~” + ‘Date_Month’ as ‘Date_YearDate_Month’
,sum(q. ‘Total Sales’) as ‘Total Sales’
–, sum(q. ‘New_Subscriptions’) as ‘New Subs’
–, sum(q. ‘Returning_Subscriptions’) as ‘Returning Subs”
–, sum(q.’Forecast’) as ‘Forecast’
,(sum(q. ‘Total_Sales’)/sum (q. ‘Previous_Total_Sales’)) -1 as ‘YoY’
–, sum(q. ‘Target’) as ‘Target’;
q = order q by (‘Date_YearDate_Month’ asc);
q = limit q 2000;
The toggle selections commenting outline within the SAQL comments feature measures the data you didn’t know you could visualize, allowing you to see multiple combo chart types with KPIs across multiple number types.
