A Combo-Chart Hack

Sometimes you have a really great idea for a chart. You know exactly what you want, you have the calculations ready only to realize that the specific chart in mind does not 100% support what you want to do. It’s a typical use case that pops up on a regular basis. A few weeks ago my colleague Terry Wilson had a scenario where a combo-chart had three measures: Amount This Year, Amount Last Year and the difference. Not a big problem, right? But the thing is the difference could be negative and that should be shown as red numbers where positive numbers should be in green. As you can see below that proves a little tricky.

Getting the basic calculations

In order for us to look at the hack done in this use case, we need to have the basic created. I have used my ‘Opportunities’ dataset and grouped by ‘Close Date’ Month.

The next thing is to switch to a compare table since we want to have the amount from two different years as well as create our calculated field the difference between the two years. In the top right corner click on the table mode and choose compare table.

Now you can add two ‘Sum of Amount’ as measures. We need to apply a filter to each of our new measures. The first measure needs to show amount last year and the other should show amount this year. You can add a filter by clicking on the arrow next to the measure and select ‘Add a Filter’. Choose the ‘Close Date’ as the field you want to base your filter off.

Next, we need to select a time frame, for this specific case it’s easier to click on the ‘Custom’ option.

This way you can select relative dates (in the tab) and drag the slider to include rows with a date from 1 year ago to 1 year ago, in other words, last year. Once done you can click ‘Add’.

The next task is to repeat this step for the second measure only this time it should be from current year to current year.

With that done it’s time to create the calculated field ‘Difference’. If you have worked with compare tables before you know that all we have to do is add a placeholder measure that we can go and edit (this becomes a little easier with Summer19). So in my case, I have simply added another ‘Sum of Amount’. In the dropdown menu for the field select ‘Edit this column’.

We do need to give this column a name and since we need to modify the SAQL in a little bit I prefer to fill out both the ‘Alias’ and the ‘Header’ – I call them both ‘Difference’. And finally, we need our calculation which in this case is ‘A-B’. Make sure to hit ‘Apply’ once you are done.

I do like to keep my naming clear when I work with SAQL, so let’s switch over to column ‘B’ by clicking the arrow in the right side next to ‘Editing Column’ header.

I am calling both the header and alias of column B ‘CurrentYr’ and column A will be ‘LastYr’.

Now if you switch to the chart mode and select a time combo chart we can modify the axis by selecting ‘Single Axis’ as the axis mode under ‘Time Combo Chart’.

We can also modify what is shown as what in the chart by selecting ‘Customize Series’ under the Series section. Carefully choosing each measure you can change whether it is shown as a line or a bar. I want my ‘LastYr’ and ‘CurrentYr’ to be shown as a line but the difference as a bar.

If you explore the conditional formatting tab you will notice that this chart type allow you to choose a color for each measure, but we can’t choose the binning option for just one measure. This is where Kaush Ruparel suggested a great solution (or a little creative hack).

Defining positive and negative values

As I mentioned we will be digging into the SAQL for this one. So the first thing we want to do is to switch to SAQL mode by selecting ‘SAQL Mode’ in the top right corner.

You SAQL query should look something like below. We first load our dataset, then generate two streams with two different filters, next we join the two streams by doing a full join on the month and then project our fields including calculating the difference field. So in other words exactly what we defined in our compare table.

q = load "opportunities";
q_A = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["1 year ago".."1 year ago"];
q_B = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["current year".."current year"];
result = group q_A by 'CloseDate_Month' full, q_B by 'CloseDate_Month';
result = foreach result generate coalesce(q_A.'CloseDate_Month', q_B.'CloseDate_Month') as 'CloseDate_Month', sum(q_A.'Amount') as 'LastYr', sum(q_B.'Amount') as 'CurrentYr';
result = foreach result generate 'CloseDate_Month', 'LastYr', 'CurrentYr', LastYr - CurrentYr as 'Difference';
result = order result by ('CloseDate_Month' asc);
result = limit result 2000;

What we want to do is add another projection after the projections from the original SAQL query. You will see the projection highlighted below.

q = load "opportunities";
q_A = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["1 year ago".."1 year ago"];
q_B = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["current year".."current year"];
result = group q_A by 'CloseDate_Month' full, q_B by 'CloseDate_Month';
result = foreach result generate coalesce(q_A.'CloseDate_Month', q_B.'CloseDate_Month') as 'CloseDate_Month', sum(q_A.'Amount') as 'LastYr', sum(q_B.'Amount') as 'CurrentYr';
result = foreach result generate 'CloseDate_Month', 'LastYr', 'CurrentYr', LastYr - CurrentYr as 'Difference';
result = foreach result generate 'CloseDate_Month', 'LastYr', 'CurrentYr', case when 'Difference' > 0 then 'Difference' else 0 end as 'Difference_Pos', case when 'Difference' < 0 then 'Difference' else 0 end as 'Difference_Neg';
result = order result by ('CloseDate_Month' asc);
result = limit result 2000;

Let’s talk about what this new projection means. Firstly, we project our dimension or grouping ‘CloseDate_Month’, then the two measures where we applied our filters to ‘LastYr’ and ‘CurrentYr’. Instead of simply projecting the calculated ‘Difference’ field we use it in two case statements. A case statement allows us to define different criteria and based on how the criteria match different outputs. The first case statement allows us to evaluate is the calculated ‘Difference’ field is greater than zero, if it is then we take the value of the ‘Difference’ field else we give it the value of 0 (zero) and we call the new projected field ‘Difference_Pos’. The second case statement does the opposite of the first. If ‘Difference’ is less than zero then we take the value of ‘Difference else we put 0 (zero) as a value and we call this new value ‘Difference_Neg’.

result = foreach result generate 'CloseDate_Month', 'LastYr', 'CurrentYr', case when 'Difference' > 0 then 'Difference' else 0 end as 'Difference_Pos', case when 'Difference' < 0 then 'Difference' else 0 end as 'Difference_Neg'; 

With the new ‘foreach’ statement, we are essentially splitting our difference field into positive and negative values into two new fields. If you switch your SAQL query back to the combo chart you can now make the ‘Difference_Pos’ and ‘Difference_Neg’ into bars under the series section as we did previously.

As you can see random colors are assigned to the two ‘Difference’ fields, however, if you go to the conditional formatting section you can apply conditional formatting to all the measures. Hence we can show the ‘Difference_Pos’ in green and the ‘Difference_Neg’ in red.

Once you have applied the conditional formatting it should look something like below. One chart showing the amount for last year and current year as well as the difference between the two with positive in green and negative in red.

Written by

Rikke Hovgaard

I have worked with CRM Analytics since 2015 (back then Wave). I started this blog to help make the journey to master CRM Analytics easy by documenting tips, tricks & cool solutions.

See author's posts