‘And’… ‘Or’… but how?
Note! From Summer 19 filter logic is available without SAQL
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’.
Written by
When specifying filter conditions for a lens/step, the UI and the compact query form always assume the AND operator between multiple filter conditions. In SAQL, multiple filter conditions can be joined with an OR operator. Is there any way to specify OR as the operator between multiple filter conditions in the UI or compact query form?
Is there an easy way to calculate the number of companies that have both conditions met. Pretty much to add AND between the two sets of filters instead of OR. I was thinking as a solution after you generate this list of companies in the second column would be 2 if they meet both filters and then write the new query as a count of records where the count of rows = 2. But not sure how to make this generic and applicable to other problems since I met have more than 2 filters in the global filter. Thank you and I hope somebody knows the answer
Are we able to show the companies that meet both sets of filters and if there is no company like that show 0? The hypothesis is we have some conglomerates in our dataset. Instead of OR putting AND between sets of filters. I manage to do this by ranking and then showing but this works in case I have at least one company that meet requirements. Thanks and regards.