Share
After you finish your workout, share a screenshot of your solutions or interesting insights.
Either on Twitter using the hashtags #WOW2024 and #CRMA and tag @genetis, @LaGMills and @msayantani. (Or you can use this handy link to do that)
Or on LinkedIn, tagging Alex Waleczek, Lauren Mills, Sayantani Mitra and Phillip Schrijnemaekers using the hashtags #WOW2024
Also make sure to fill out the Submission Tracker to track your progress and help us judge the difficulty of our challenges.
Solution
So, first of all, let’s contextualize the problem
If we create a query with more than 1 grouping and sort the query by our metric within the groups, this is an example of the result we get, if we check the query advanced editor:
“orders”: [
{
“name”: “Account.Industry”,
“ascending”: true,
“filters”: []
},
{
“name”: “A”,
“ascending”: false,
“filters”: []
}
]
Notice thatI mentioned we have 2 groupings in our query, but to maintain the metric sorted within the groups, the query applies only the first grouping in our sorting, and then the metric. If we have 3 groupings, this pattern will repeat with the first 2 groupings. For 4 groupings this will repeat with the first 3, etc. In other words, to maintain our query sorted within our groupings, the last grouping should be excluded from our sorting.
But what if we select more than 2 groupings, in a multi-select column interaction, to change the order of our groupings dynamically? We’d have to add and remove code blocks from our sorting order in the advanced editor for each grouping we select in the static step, like this:
“orders”: [
{
“name”: “Account.Type”,
“ascending”: true,
“filters”: []
},
{
“name”: “Account.Industry”,
“ascending”: true,
“filters”: []
},
{
“name”: “Type”,
“ascending”: true,
“filters”: []
},
{
“name”: “A”,
“ascending”: false,
“filters”: []
}
]
I’ve managed to do that by using SAQL. Let’s see how is the same format in code format:
q = order q by (‘Account.Industry’ asc, ‘A’ desc);
Now, create a static step for your grouping, and add a column beside with the sorting order for it. Example:
Now, we will have to create a hidden query to get our correct sorting and use this query’s result to order our original chart query (pay attention to the comments, explaining the query created):
q = load “opportunity”;
— filter it by a single row to keep query performance the best possible
q = filter q by ‘Id’ == “0064W00000u7SghQAE”;
— Use the replace formula to replace the last grouping of the query by a null value, and the slice data manipulation function to get the last grouping of the selection, and eliminate it.
q = foreach q generate replace(“{{column(static_1.selection, [“Text3″]).asObject()}}”,”{{slice(column(static_1.selection, [“Text3″]),-1).asObject()}}”,””) as ‘slice’;
— Disable global filters and any faceting applied to this query, as it should always display a value (otherwise, your original query would display unwanted results)
Lastly, get this hidden query result, and apply it to our sorting order in our original query.
q = order q by ({{cell(orderquery_1.result, 0, “slice”).asString()}}’A’ desc);
— Notice that our query excluded the metric, so we are adding it right after the result query
Et voilà! You now can sort your query within your groupings regardless of the order of your multi select column interaction.
