Single Bar with Stacked Bar Chart (CRM Analytics)

Let me tell you people about how I solved a problem in Workout Wednesdays.

So this was a challenge by Phillip Schrijnemaekers which displays the open amount in one bar and the Closed Won and Closed Lost amount (both) in another bar. WOW Challenge Week 03.

So one might think that how do we combine simple column charts with stacked column charts? We can’t. We have to be clever enough to find a way to make it work. CRM Analytics does not provide us the option to combine 2 out of three measures into one stacked bar and keep the other one as is.

So, to start, let’s do all we can using the UI. After loading the dataset into CRM Analytics, you will have a dataset created. Click on “Create” → “Dashboard” → “Create Blank Dashboard” and a new dashboard will appear.

Then pull a chart widget (left side panel top) and click ‘Chart’. Now let’s start creating the query.

According to this challenge, you will think let’s create 4 streams of data

• Open
• Closed
• Won
• Lost

But think a bit, what is Won and Lost? Aren’t they just parts of Closed? Won and Lost is nothing but Closed Won and Closed Lost. So, create two streams of data → Sum of Amount but one filtered with Closed = False and the other with Closed = True. See what I did there? I just created 2 streams instead of 4, with the first one giving us Open Amount and the second one giving us the closed amount. At this point we will have 2 measures on the chart.

Convert the chart into Stacked Column Chart and add Account Name as Bars. It will look exactly like a simple column chart (because we haven’t given it the Bar Segment parameter yet) — but just trust the process.

So now let’s dive into the SAQL (coding part) because this is where things get interesting.

Step 1:

These are the first 3 lines of the SAQL

q = load “WOWweek3”;
q_B = filter q by 'Closed' == "true";
q_A = filter q by 'Closed' == "false";

Keep them as is — these are the two data streams we have created.

Step 2:

Now, create q1 and q2 — two data streams. q1 handles the Open Amounts and the Account Names…

q1 = group q_A by ('Account_Name');
q1 = foreach q1 generate q_A.'Account_Name' as 'Account_Name', 
     sum(q_A.'Amount') as 'Open';

…while q2 handles the Closed Won and Closed Lost Amounts along with their Account Name and Stage. Notice we are using Stage here as it is important to give this parameter to the Bar Segments to form the stack for these two kids.

q2 = group q_B by ('Account_Name', 'Stage');
q2 = foreach q2 generate q_B.'Account_Name' as 'Account_Name',
     q_B.'Stage' as 'Stage', sum(q_B.'Amount') as 'Closed', 
     case when ('Stage' == "Closed Won") then sum(q_B.'Amount') end as 
     'Won Amount',
     case when ('Stage' == "Closed Lost") then sum(q_B.'Amount') end as
     'Lost Amount';

Step 3:

Now the magic. UNION. We have to know how Union works in SAQL (it just pastes one table/dataframe below another).

q3 = union q1, q2;

We are creating a third data stream q3 which unionises q1 and q2 and forms the final data table. Our table should look something like this:

Looks wrong but just hold on.

Step 4:

We’re almost there. The final step. We now project our data properly and order it as asked.

q3 = foreach q3 generate 'Account_Name', 
     case when 'Stage' is null then "Open" 
     when 'Stage' == "Closed Won" then "Won" else "Lost" end as 'Stage', 
     coalesce(sum('Open'), 0) as 'Open', 
     coalesce(sum('Closed'), 0) as 'Closed', 
     coalesce(sum('Won Amount'), 0) as 'Won', 
     coalesce(sum('Lost Amount'), 0) as 'Lost';
q3 = order q3 by ('Won' desc);

I don’t like to keep nulls in my final dataset so I’m replacing it with 0s using coalesce() function.

Stage won’t have any value from the data we have brought from q1 data stream because we haven’t grouped it by ‘Stage’. But q2 will have it. So it’s necessary to have this case when ‘Stage’ is null then “Open” statement. This gives the required values to the Open Amount stage.

SAQL part done. Uff.

Now return back to chart mode by clicking the first button of the three.

Hide the Won and Lost measures.

Now our final chart should look something like this. Which is what the challenge asks for! Voila! Its done!

So this is how we Unionise two data streams to produce a chart which contains a mix of simple and stacked bars (Its a stacked bar chart anyways).

Let me know how you liked this! This is my first article on Medium.

Salesforce CRM Analytics