Query Patterns & Examples

This guide describes common query patterns and example SAQL queries used in Advanced Analytics dashboards.

Cloudaware Advanced Analytics leverages Salesforce CRM Analytics for building dashboards. SAQL (Salesforce Analytics Query Language) is the primary query language used in CRM Analytics widgets. Widgets bind to queries defined in either visual mode (steps, groupings, filters) or SAQL code mode for advanced logic.

SAQL is SQL-like but optimized for datasets, supporting bind variables for interactivity. Access it in Edit Dashboard → Queries panel → Edit Query → Query Mode.

Query patterns in this guide assume you are working with the metrics and dimensions defined in the Advanced Analytics Data Model.

How Queries Work in Widgets

Every chart, table, KPI, or other widget on a dashboard connects to a query that pulls and transforms data from a dataset. In the dashboard editor:

• Visual Mode: Drag-and-drop to build groupings (dimensions), measures (aggregations), sorts, and limits—no code needed.
• Query Mode: Switch to this mode to write raw SAQL for complex joins, case statements, foreach loops, or bindings (for example, q = load "dataset"; q = group q by 'StageName'; q = foreach q generate 'StageName', sum('Amount') as 'Total';).

See also: Manage Queries for Widgets

SAQL for Common Tasks

• Basic Aggregation: load "opportunity_dataset" | group by 'Region' | foreach generate 'Region', count() as 'Opps', sum('Amount') as 'Pipeline'.
• Bindings/Facets: Use {{cell.Region_1.selection[0].label}} to link filters across widgets dynamically.
• Custom Steps: Conditional logic like case when 'Amount' > 100000 then "High" else "Low" end.

Examples

Below are examples of queries for widgets:

Break Values into Top 5 (10, 20, Etc.) and 'Other'

The query helps to break all values into a dynamic top group (for example, Top 5) and everything else under 'Other'.

q = load "AWS_Daily_Cost_and_Usage_with_Custom_Metrics";
q = group q by 'billingTag_product';
q = filter q by ('billingTag_product' != "N/A" || 'billingTag_product' is null);
q = foreach q generate (case when (rank() over([..] partition by all order by (sum('NetAmortizedCost') desc, 'billingTag_product' desc ))) <= 5 then 'billingTag_product' else "Other" end) as 'billingTag_product', sum('NetAmortizedCost') as 'NetAmortizedCost';
q = group q by 'billingTag_product';
q = foreach q generate 'billingTag_product' as 'billingTag_product', sum('NetAmortizedCost') as 'NetAmortizedCost';
q = order q by 'NetAmortizedCost' desc;
q = limit q 10000;

This SAQL query processes AWS cost data from a dataset, creating a top-5 products by cost pie chart (or similar visualization) by grouping costs by product tag, excluding "N/A" values, ranking products within each group by descending amortized cost, collapsing lower-ranked items into an "Other" category, re-aggregating, sorting by cost, and limiting results.

Monthly and Daily Deltas

The query helps to create a text widget that shows the period-over-period change with an arrow (▼, ▲ or ◄ ►) and is highlighted with red, green, or gray color depending on the query result.

tip

Monthly and Daily Deltas should be created as separate queries, and then used in another widget.

Monthly Delta query:

q = load "AWSMonthlyCostAndUsage";
monthago = filter q by date('ReportDate_Year', 'ReportDate_Month', 'ReportDate_Day') in ["1 month ago".."1 month ago"];
two_months_ago = filter q by date('ReportDate_Year', 'ReportDate_Month', 'ReportDate_Day') in ["2 month ago".."2 month ago"];
c = group two_months_ago by all full, monthago by all;
c = foreach c generate sum(monthago['NetAmortizedCost']) - sum(two_months_ago['NetAmortizedCost']) as 'diff';
c = foreach c generate 'diff' as 'diff', (case when 'diff' > 0 then "#ED3251" when 'diff' < 0 then "#4ED469" else "#7D98B3" end) as 'color', (case when 'diff' < 0 then "▼" when 'diff' > 0 then "▲" else "◄ ►" end) as 'diff_arrow';

This SAQL query calculates AWS Monthly Cost Delta (1 month ago minus 2 months ago) and adds conditional color-coding and arrows for a KPI widget—perfect for dashboard trend indicators showing if costs are rising (red ▲) or falling (green ▼).

Daily Delta query:

q = load "AWS_Daily_Cost_and_Usage_with_Custom_Metrics";
yesterday = filter q by date('LineItemUsageStartDate_Year', 'LineItemUsageStartDate_Month', 'LineItemUsageStartDate_Day') in ["3 day ago".."3 day ago"];
two_days_ago = filter q by date('LineItemUsageStartDate_Year', 'LineItemUsageStartDate_Month', 'LineItemUsageStartDate_Day') in ["4 day ago".."4 day ago"];
c = group two_days_ago by all full, yesterday by all;
c = foreach c generate sum(yesterday['NetAmortizedCost']) - sum(two_days_ago['NetAmortizedCost']) as 'diff';
c = foreach c generate 'diff' as 'diff', (case when 'diff' > 0 then "#ED3251" when 'diff' < 0 then "#4ED469" else "#7D98B3" end) as 'color', (case when 'diff' < 0 then "▼" when 'diff' > 0 then "▲" else "◄ ►" end) as 'diff_arrow';

This SAQL query calculates AWS Daily Cost Delta ("3 days ago" minus "4 days ago") with visual indicators (color + arrows) for a KPI widget—nearly identical to your monthly version but using daily granularity.

tip

You can also calculate the percentage difference by changing the query formula from 'a' - 'b' to (('a' - 'b') * 100) / 'b', where 'a' = 'yesterday' and 'b' = '2daysago', for example.

Timeseries Forecast

This query uses the 'time-series' operator to create a forecast.

In the example below, we create a 3-day Forecast for Amortized Cost for AWS Daily CUR (Today’s date has 2 points - actual and forecasted cost):

q = load "AWS_Daily_Cost_and_Usage_Joined";

q = filter q by date('LineItemUsageEndDate_Year', 'LineItemUsageEndDate_Month', 'LineItemUsageEndDate_Day') in ["1 month ago".."current day"];

q = group q by ('LineItemUsageEndDate_Year', 'LineItemUsageEndDate_Month', 'LineItemUsageEndDate_Day');

ts = foreach q generate 'LineItemUsageEndDate_Year', 'LineItemUsageEndDate_Month', 'LineItemUsageEndDate_Day', 'LineItemUsageEndDate_Year' + "~~~" + 'LineItemUsageEndDate_Month'+"~~~"+ 'LineItemUsageEndDate_Day' as 'LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day', sum('AmortizedCost') as 'AmortizedCost';

ts = fill ts by (dateCols=('LineItemUsageEndDate_Year','LineItemUsageEndDate_Month','LineItemUsageEndDate_Day',"Y-M-D"));

ts = timeseries ts generate 'AmortizedCost' as 'predicted' with (dateCols=('LineItemUsageEndDate_Year','LineItemUsageEndDate_Month', 'LineItemUsageEndDate_Day',"Y-M-D"), length=4, ignoreLast=true, model="multiplicative");

ts = foreach ts generate 'LineItemUsageEndDate_Year' + "~~~" + 'LineItemUsageEndDate_Month'+"~~~"+ 'LineItemUsageEndDate_Day' as 'LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day', case when 'AmortizedCost' is null then 'predicted' else null end as 'predicted_cut';

ts = timeseries ts generate 'predicted_cut' as 'predicted_2' with (order=('LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day' desc), length=0, ignoreLast=false, model="multiplicative");

s = foreach q generate 'LineItemUsageEndDate_Year', 'LineItemUsageEndDate_Month', 'LineItemUsageEndDate_Day', 'LineItemUsageEndDate_Year' + "~~~" + 'LineItemUsageEndDate_Month'+"~~~"+ 'LineItemUsageEndDate_Day' as 'LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day', case when date('LineItemUsageEndDate_Year', 'LineItemUsageEndDate_Month', 'LineItemUsageEndDate_Day') in ["current day".."current day"] then null else sum('AmortizedCost') end as 'AmortizedCostWithoutLastDay', sum('AmortizedCost') as 'AmortizedCost';

result = cogroup ts by ('LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day') full, s by ('LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day');

result = foreach result generate ts.'LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day' as 'LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day', sum(s.'AmortizedCostWithoutLastDay') as 'AmortizedCostWithoutLastDay', sum(s.'AmortizedCost') as 'AmortizedCost', sum(ts.'predicted_2') as 'Forecasted Cost';

result = foreach result generate 'LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day' as 'LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day', 'AmortizedCost' as 'Actual Cost', 'AmortizedCostWithoutLastDay' as 'AmortizedCostWithoutLastDay', 'Forecasted Cost' as 'Forecasted Cost', case when 'AmortizedCostWithoutLastDay' is null then 'Forecasted Cost' else 'AmortizedCost' end as 'Actual Cost with Forecast' ;

result = order result by 'LineItemUsageEndDate_Year~~~LineItemUsageEndDate_Month~~~LineItemUsageEndDate_Day' asc;

This SAQL query creates an AWS daily cost forecasting model that generates future cost predictions using time series analysis, blending actual costs with forecasts for visualization—handling incomplete current periods by excluding today's partial data.

Stacked Bar Chart with % for Tags

The query takes tags, puts the tag names into 1 column, and counts the number of tagged and untagged for each tag under the coverage column.

In the example below, we have 8 tags: 6 apply to all resources, and 2 may not apply to some resources. So there can be Compliant, Incompliant, or Not Applicable compliance states that define the coverage %.

q = load "Tagging_Coverage_Updated";
q = filter q by (!('Type_formula' in ["AWS Athena Work Group", "AWS Cloudwatch Logs Log Group", "AWS EBS Snapshot", "AWS EC2 Security Group", "AWS ECS Task Definition", "AWS EFS File System", "AWS RDS Snapshot", "AWS VPC Internet Gateway", "AWS Workspace", "AWS EC2 Elastic IP"]) || 'Type_formula' is null);
q = foreach q generate count(q) as total, caTag_Environment__c_formula as caTag_Environment__c_formula, caTag_Service__c_formula as caTag_Service__c_formula, caTag_Component__c_formula as caTag_Component__c_formula, caTag_SupportGroup__c_formula as caTag_SupportGroup__c_formula, caTag_Compliance__c_formula as caTag_Compliance__c_formula, caTag_IaC__c_formula as caTag_IaC__c_formula, caTag_Product__c_formula as caTag_Product__c_formula, caTag_PipelineIdentifier__c_formula as caTag_PipelineIdentifier__c_formula;
q_AA = foreach q generate (case when caTag_Environment__c_formula == "Incompliant" then 0 else 1 end) as tagged, "CompanyName Environment" as Tag;
q_AA = group q_AA by Tag;
q_AA = foreach q_AA generate Tag, "Compliant" as Compliance, sum(tagged) as 'Compliant';
q_AB = foreach q generate (case when caTag_Environment__c_formula == "Incompliant" then 1 else 0 end) as 'tagged', "CompanyName Environment" as Tag;
q_AB = group q_AB by Tag;
q_AB = foreach q_AB generate Tag, "Incompliant" as Compliance, sum('tagged') as 'Compliant';
q_BA = foreach q generate (case when caTag_Service__c_formula == "Incompliant" then 0 else 1 end) as tagged, "CompanyName Service" as Tag;
q_BA = group q_BA by Tag;
q_BA = foreach q_BA generate Tag, "Compliant" as Compliance, sum(tagged) as 'Compliant';
q_BB = foreach q generate (case when caTag_Service__c_formula == "Incompliant" then 1 else 0 end) as 'tagged', "CompanyName Service" as Tag;
q_BB = group q_BB by Tag;
q_BB = foreach q_BB generate Tag, "Incompliant" as Compliance, sum('tagged') as 'Compliant';
q_CA = foreach q generate (case when caTag_Component__c_formula == "Incompliant" then 0 else 1 end) as tagged, "CompanyName Component" as Tag;
q_CA = group q_CA by Tag;
q_CA = foreach q_CA generate Tag, "Compliant" as Compliance, sum(tagged) as 'Compliant';
q_CB = foreach q generate (case when caTag_Component__c_formula == "Incompliant" then 1 else 0 end) as 'tagged', "CompanyName Component" as Tag;
q_CB = group q_CB by Tag;
q_CB = foreach q_CB generate Tag, "Incompliant" as Compliance, sum('tagged') as 'Compliant';
q_DA = foreach q generate (case when caTag_SupportGroup__c_formula == "Incompliant" then 0 else 1 end) as tagged, "CompanyName Support Group" as Tag;
q_DA = group q_DA by Tag;
q_DA = foreach q_DA generate Tag, "Compliant" as Compliance, sum(tagged) as 'Compliant';
q_DB = foreach q generate (case when caTag_SupportGroup__c_formula == "Incompliant" then 1 else 0 end) as 'tagged', "CompanyName Support Group" as Tag;
q_DB = group q_DB by Tag;
q_DB = foreach q_DB generate Tag, "Incompliant" as Compliance, sum('tagged') as 'Compliant';
q_EA = foreach q generate (case when caTag_Compliance__c_formula == "Compliant" then 1 else 0 end) as tagged, "CompanyName Compliance" as Tag;
q_EA = group q_EA by Tag;
q_EA = foreach q_EA generate Tag, "Compliant" as Compliance, sum(tagged) as 'Compliant';
q_EB = foreach q generate (case when caTag_Compliance__c_formula == "Incompliant" then 1 else 0 end) as 'tagged', "CompanyName Compliance" as Tag;
q_EB = group q_EB by Tag;
q_EB = foreach q_EB generate Tag, "Incompliant" as Compliance, sum('tagged') as 'Compliant';
q_EB1 = foreach q generate (case when caTag_Compliance__c_formula == "Not Applicable" then 1 else 0 end) as 'tagged', "CompanyName Compliance" as Tag;
q_EB1 = group q_EB1 by Tag;
q_EB1 = foreach q_EB1 generate Tag, "Not Applicable" as Compliance, sum('tagged') as 'Compliant';
q_FA = foreach q generate (case when caTag_IaC__c_formula == "Incompliant" then 0 else 1 end) as tagged, "CompanyName IaC" as Tag;
q_FA = group q_FA by Tag;
q_FA = foreach q_FA generate Tag, "Compliant" as Compliance, sum(tagged) as 'Compliant';
q_FB = foreach q generate (case when caTag_IaC__c_formula == "Incompliant" then 1 else 0 end) as 'tagged', "CompanyName IaC" as Tag;
q_FB = group q_FB by Tag;
q_FB = foreach q_FB generate Tag, "Incompliant" as Compliance, sum('tagged') as 'Compliant';
q_GA = foreach q generate (case when caTag_Product__c_formula == "Incompliant" then 0 else 1 end) as tagged, "CompanyName Product" as Tag;
q_GA = group q_GA by Tag;
q_GA = foreach q_GA generate Tag, "Compliant" as Compliance, sum(tagged) as 'Compliant';
q_GB = foreach q generate (case when caTag_Product__c_formula == "Incompliant" then 1 else 0 end) as 'tagged', "CompanyName Product" as Tag;
q_GB = group q_GB by Tag;
q_GB = foreach q_GB generate Tag, "Incompliant" as Compliance, sum('tagged') as 'Compliant';
q_HA = foreach q generate (case when caTag_PipelineIdentifier__c_formula == "Not Applicable" then 1 else 0 end) as tagged, "CompanyName Pipeline Identifier" as Tag;
q_HA = group q_HA by Tag;
q_HA = foreach q_HA generate Tag, "Not Applicable" as Compliance, sum(tagged) as 'Compliant';
q_HA1 = foreach q generate (case when caTag_PipelineIdentifier__c_formula == "Compliant" then 1 else 0 end) as tagged, "CompanyName Pipeline Identifier" as Tag;
q_HA1 = group q_HA1 by Tag;
q_HA1 = foreach q_HA1 generate Tag, "Compliant" as Compliance, sum(tagged) as 'Compliant';
q_HB = foreach q generate (case when caTag_PipelineIdentifier__c_formula == "Incompliant" then 1 else 0 end) as 'tagged', "CompanyName Pipeline Identifier" as Tag;
q_HB = group q_HB by Tag;
q_HB = foreach q_HB generate Tag, "Incompliant" as Compliance, sum('tagged') as 'Compliant';
result = union q_AA, q_AB, q_BA, q_BB, q_CA, q_CB, q_DA, q_DB, q_EA, q_EB, q_EB1, q_FA, q_FB, q_GA, q_GB, q_HA, q_HA1, q_HB;
result = group result by ('Tag', 'Compliance');
result = foreach result generate Tag, Compliance, sum(Compliant) as Compliant;

This SAQL query analyzes AWS resource tagging compliance across 8 custom tag fields, generating a unified report of compliant/incompliant counts per tag type. It excludes certain AWS resource types, computes binary compliance flags (0/1), creates separate compliant/incompliant streams for each tag, then unions them for final aggregation.