‘And’… ‘Or’… but how?

Filters are a great way to segment your data to get valuable insights, since having too broad a view do not help you understand the details of what is going on in your business. Einstein Analytics makes it easy for you to drill in and get that insight by allowing you to add list widgets, toggles as well as selecting insight from other charts; with few clicks the dashboard changes and filters based on your selections.

Sometimes this behavior is not enough. Sometimes you want to apply filter logic to a chart, just like we can do in standard Salesforce reports. You want to be able to see leads with the lead source ‘Website’ and industry ‘Media’ OR lead source ‘Cold Call’ and industry ‘Telecommunications’. Okay, this is a totally made up example that probably doesn’t apply to any businesses, but my point is you want to apply filter logic to your filters.

Filter logic is possible in Einstein Analytics but as of Summer 18 it is not yet available in the UI – let’s hope it will be soon. Though it’s not in the UI, that doesn’t mean you should let it stop you. With a simple modification in the SAQL we can achieve the filter logic we want.

Filter Logic? Show me how!

Okay, let’s use the example from above.

  • Object: Lead
  • Measure: Count of rows
  • Group: Company
  • Filter: Lead source ‘Website’ and industry ‘Media’ OR lead source ‘Cold Call’ and industry ‘Telecommunications’

First, let’s create a step with the measure count of rows and grouped by company, this step doesn’t account for the filter logic but it does have two filters; the industry is ‘media’ and the lead source’website’.

The next thing is to switch to SAQL mode by clicking the ‘>_’ button in the top right corner.

Now it’s time for a little copy paste. We want to make sure our ‘q = filter’ is in just one line. So copy the ‘Industry’ == “Media” and paste it just after “Website”. Between the two statements type ‘and’. Delete the second ‘q = filter q by…’ – the line where you copied the industry from. It should look something like this now:

q = load "Leads";
q = filter q by 'LeadSource' == "Website" and 'Industry' == "Media";
q = group q by 'Company';
q = foreach q generate 'Company' as 'Company', count() as 'count';
q = order q by 'Company' asc;
q = limit q 2000;

Let’s wrap the new filter in parentheses.

q = load "Leads";
q = filter q by ('LeadSource' == "Website" and 'Industry' == "Media");
q = group q by 'Company';
q = foreach q generate 'Company' as 'Company', count() as 'count';
q = order q by 'Company' asc;
q = limit q 2000;

The requirement had another filter lead source ‘cold call’ and industry ‘telecommunications’. We do this easiest by copying the existing filter and replacing the values to match. So copy the filter in the parentheses and paste it just after the existing filter and make sure to correct the values in the double quotes; you might notice the values to change have a slight green color.

q = load "Leads";
q = filter q by ('LeadSource' == "Website" and 'Industry' == "Media")('LeadSource' == "Cold Call" and 'Industry' == "Telecommunications");
q = group q by 'Company';
q = foreach q generate 'Company' as 'Company', count() as 'count';
q = order q by 'Company' asc;
q = limit q 2000;

Before we run the query we need to add a ‘or’ between the two sets of filters.

q = load "Leads";
q = filter q by ('LeadSource' == "Website" and 'Industry' == "Media") or ('LeadSource' == "Cold Call" and 'Industry' == "Telecommunications");
q = group q by 'Company';
q = foreach q generate 'Company' as 'Company', count() as 'count';
q = order q by 'Company' asc;
q = limit q 2000;

Now hit ‘run’ to see the result of your changes.

You can now switch back to chart mode and choose the graph you would like to use. Notice when you switch that the filters are now locked since you have modified them in SAQL.

And that is all folks! There is not much more to filter logic in Einstein Analytics other than instead of ‘and’ you can use && and you can use || for ‘or’.


Leave a Reply

Your email address will not be published. Required fields are marked *