Trellis charts in Einstein Analytics can be very powerful as your data-ink ratio is very high. You can increase the value of this charts by adding additional information like the average. You might want to check out my previous post for the general setup of Trellis.

There are two different approaches to add additional information like average.

• Compare table
• SAQL

The faster route is probably the  Compare table as you can do everything via UI. Depending on the requirements you still might need to get into SAQL to get to your last mile and increase the value of the chart.

Adding the average via Compare Table

In edit mode I opened the Trellis chart and changed the chart type to “Compare table”.  I added an additional metric which I edited via the UI. To calculate the average I leveraged the formula builder and used the partition function. The partition in this example is only on the time dimension/grouping and not on the Industries.

avg(A) over ([..] partition by ('CloseDate_Year~~~CloseDate_Quarter'))

 

After applying the Formula I set the chart type to “Time Combo” as I want to see the sum as bars and the average as lines, Axis mode is set to “Single Axis” so both values can be compared.

 

With a few simple steps, the average is added and the user can spot easily differences between the industries, seasonalities and outliers.

Adding the average via Compare Table

As I wanted to add the average as separate Trellis element the Compare Table couldn’t help me anymore. With SAQL I can easily add another stream where I calculated the average and afterwards unioned it with my existing industries. Important is that the average element is easily distinguishable (colour / name / sort order) from the other industries.

Step 1  – switch into SAQL mode of your Trellis Chart

In the SAQL editor, you will find your current query which will look like this.

q = load "Sample";
a = group q by ('Account.Industry', 'CloseDate_Year', 'CloseDate_Quarter');
a = foreach a generate 'Account.Industry' as 'Account.Industry', 'CloseDate_Year' + "~~~" + 'CloseDate_Quarter' as 'CloseDate_Year~~~CloseDate_Quarter', sum('Amount') as 'sum_Amount';
a = order a by ('Account.Industry' asc, 'CloseDate_Year~~~CloseDate_Quarter' asc);

Step 2  – add average calculation and union it

To generate an additional Trellis element I had to generate this first by copy/paste the existing stream “a” but this time I don’t want to group by the Trellis-Dimension (e.g. Industry). After pasting stream “a” at the end I deleted the “Industry” in the grouping and set the ‘Industry” with “Ø Average” in the foreach statement.  As last step, I combined stream “a” and “b” with a union.

q = load "Sample";
a = group q by ('Account.Industry', 'CloseDate_Year', 'CloseDate_Quarter');
a = foreach a generate 'Account.Industry' as 'Account.Industry', 'CloseDate_Year' + "~~~" + 'CloseDate_Quarter' as 'CloseDate_Year~~~CloseDate_Quarter', sum('Amount') as 'sum_Amount';
a = order a by ('Account.Industry' asc, 'CloseDate_Year~~~CloseDate_Quarter' asc);
b = group q by ('CloseDate_Year', 'CloseDate_Quarter');
b = foreach b generate "Ø Average" as 'Account.Industry', 'CloseDate_Year' + "~~~" + 'CloseDate_Quarter' as 'CloseDate_Year~~~CloseDate_Quarter', avg('Amount') as 'avg_Amount';
b = order b by ('Account.Industry' asc, 'CloseDate_Year~~~CloseDate_Quarter' asc);
c = union a,b;

 

Step 3  – add projection after “union”

EA needs a final projection to generate the Trellis Chart (even if other charts/tables might work) as shown below.

q = load "Sample";
a = group q by ('Account.Industry', 'CloseDate_Year', 'CloseDate_Quarter');
a = foreach a generate 'Account.Industry' as 'Account.Industry', 'CloseDate_Year' + "~~~" + 'CloseDate_Quarter' as 'CloseDate_Year~~~CloseDate_Quarter', sum('Amount') as 'sum_Amount';
a = order a by ('Account.Industry' asc, 'CloseDate_Year~~~CloseDate_Quarter' asc);
b = group a by ('CloseDate_Year~~~CloseDate_Quarter' );
b = foreach b generate "Ø Average" as 'Account.Industry', 'CloseDate_Year~~~CloseDate_Quarter'  as 'CloseDate_Year~~~CloseDate_Quarter', avg('sum_Amount') as 'avg_Amount';
b = order b by ('Account.Industry' asc, 'CloseDate_Year~~~CloseDate_Quarter' asc);
c = union a,b;
c= group c by ('Account.Industry', 'CloseDate_Year~~~CloseDate_Quarter');
c = foreach c generate 'Account.Industry' as 'Account.Industry', 'CloseDate_Year~~~CloseDate_Quarter' as 'CloseDate_Year~~~CloseDate_Quarter', sum('sum_Amount') as 'sum_Amount', sum('avg_Amount') as 'avg_Amount', case when 'Account.Industry' == "Ø Average" then 0 else avg(sum('sum_Amount')) over ([..] partition by 'CloseDate_Year~~~CloseDate_Quarter') end as 'Average';
c = order c by ('Account.Industry' asc, 'CloseDate_Year~~~CloseDate_Quarter' asc);

After this step, we have a clearly visible new element with a separate colour. I can now easily spot my seasonality and can compare my different Industry Trellis to the average.

Step 4 – Blend the Average into your chart

You can take it a step further to blend in the average of industries in each Trellis. For this, you simply add one more Measure at the last step. The partition by function helps us here to calculate the average while ignoring the grouping of the Trellis dimension. For this visualization, you have to change the chart type to Comp-Time chart so bars and lines are shown together as an overlay. (I deleted here the projection of “avg_Amount” as I just wanted to see the line for the “Ø Average” element and use the pink default colour for the “Average”)

q = load "Sample";
a = group q by ('Account.Industry', 'CloseDate_Year', 'CloseDate_Quarter');
a = foreach a generate 'Account.Industry' as 'Account.Industry', 'CloseDate_Year' + "~~~" + 'CloseDate_Quarter' as 'CloseDate_Year~~~CloseDate_Quarter', sum('Amount') as 'sum_Amount';
a = order a by ('Account.Industry' asc, 'CloseDate_Year~~~CloseDate_Quarter' asc);
b = group a by ('CloseDate_Year~~~CloseDate_Quarter' );
b = foreach b generate "Ø Average" as 'Account.Industry', 'CloseDate_Year~~~CloseDate_Quarter'  as 'CloseDate_Year~~~CloseDate_Quarter', avg('sum_Amount') as 'avg_Amount';
b = order b by ('Account.Industry' asc, 'CloseDate_Year~~~CloseDate_Quarter' asc);
c = union a,b;
c= group c by ('Account.Industry', 'CloseDate_Year~~~CloseDate_Quarter');

c = foreach c generate 'Account.Industry' as 'Account.Industry', 'CloseDate_Year~~~CloseDate_Quarter' as 'CloseDate_Year~~~CloseDate_Quarter', sum('sum_Amount') as 'sum_Amount',avg(sum('sum_Amount')) over ([..] partition by 'CloseDate_Year~~~CloseDate_Quarter')as 'Average';
c = order c by ('Account.Industry' asc, 'CloseDate_Year~~~CloseDate_Quarter' asc)
 

 

As result, you have a clearly visible average element which is blended in each Trellis as well.

Combined with a navigation bar you can blend in and out elements you want directly to compare e.g. benchmark. The added GIF shows you the facetting between the navigation bar and the single Trellis elements.