In the complex world of data analytics, organizations are constantly asking for new and different insights from their data. One of the most common ways to gather a variety of insights is to “slice and dice” or tinker with the results by including or excluding various combinations of data points. But what if organizations didn’t have to choose between slicing or dicing? What if users could easily create an analysis that has multidimensional include and exclude criteria?
In most platforms, orchestrating complex and multidimensional filtering criteria involves creating massive nested if/then statements that are very rigid, difficult to troubleshoot, and computationally intense. In this blog post, we’ll explore how Sigma Computing offers a novel approach to solve not only complex scenarios like identifying specific items in retail orders, but also multiple levels of criteria like tracking customer behavior from retail orders.
What are Inclusionary and Exclusionary Filters, and why use Them?
The most basic use of filters is designed to reduce the amount of information in a data source. They evaluate what should be in, and what should be out.
For example, wanting to see all orders between two dates includes some orders and excludes any orders not placed during that window. Criteria can be simple such as orders placed by a certain person, or complex using multiple criteria such as orders placed by a certain person in a certain timeframe.
As the criteria become more complicated, oftentimes, the formula to evaluate the data also becomes incredibly difficult. Most data platforms require nested calculations where multiple if statements are combined into a single True/False result. Sigma Computing offers a different solution with its flexible modeling abilities.
This blog will cover how to handle two complex scenarios:
Identifying when a retail order contains a certain item but not a different type of item.
Identifying customers that have ever placed an order that contains a certain item but not a different type of item.
Scenario 1: Finding Orders That Contain MP3s but not Cell Phones
The data used in these examples is the Plugs Electronic Hands-On Lab, which can be found in Sigma’s sample database.
The first scenario we’ll cover is finding orders containing MP3s but not Cell Phones. Both of these categories are found under the Product Family so we won’t be able to use Sigma’s built-in filter. To accomplish this, we’ll first create a list of orders that meet the include criteria. Next, we’ll create a list that covers the excluded criteria. Finally, we’ll compare those two lists resulting in a list of orders that should be included but also ignoring orders that are on the exclude list.
Note: It’s best to have an understanding of Sigma’s Table Aggregation Features to understand this solution. If you’d like to learn more about this functionality, check out this blog: Aggregating Tables in Sigma Computing, or watch this video: Sigma Fundamentals: Mastering Aggregations.
Step 1: Create the Include List
Add the Plugs Electronic Hands-On Lab as a table element.
Create a page control filter for Product Family.
Group by Order Number.
Create a grouped calculation for order count.
Step 2: Create the Exclude List
Add the Plugs Electronic Hands-On Lab as a table element.
This needs to be a separate copy of the same data source, it can’t be a child element because we need the include and exclude lists to be independent of each other.
Create a page control filter for Product Family.
Group by Order Number.
Create a grouped calculation for order count.
This could be Countdistinct([Order Number]), RowNumber([Order Number]), or “1”
Step 3: Compare the Include and Exclude List
1. Add a new table element that is sourced from a join.
2. Select the Include List from the Page Elements option as a source.
3. Select the Order Number for the grouping level.
4. Add a second source.
5. Select the exclude list from Page Elements.
6. Select the Order Number for the grouping level.
7. Use a left outer join joined on Order Number.
The Include list needs to be the primary source.
8. Click Preview and Done.
Step 4: Filter the Completed List
So far, we’ve created a list of order numbers that we want to include (all orders that contain MP3s) and we’ve created a list of order numbers that we want to exclude (all orders that contain Cell Phones). We now want to tell Sigma to ignore the order numbers from the Include list, that are on the exclude this.
Add a filter on the Order Numbers from the Exclude table where the order number is null.
Since we used a left outer join we’re telling Sigma only to include the Order Numbers from the include list that are not also on the exclude list.
Scenario 2: Finding Customers who Have Never Placed an Order That Contains MP3s but not Cell Phones
For this scenario, we want to identify certain criteria at a lower level (order number) and a higher level (customer). We will apply the same principles of creating a list to include and exclude, but add an additional calculation for the extra level.
Step 1: Add the Customer Grouping Level for Include
These steps will build off of what was built for the first scenario.
In the Includes table, add Customer Name to a higher grouping.
Drag Customer Name up to groupings.
Drag the Customer Name grouping above the Order Number Grouping.
Create and Include/Exclude calculation at the Customer Name Grouping Level.
Have the value be 0.
Step 2: Add the Customer Grouping Level for Exclude
Now we will identify the customers that should be excluded.
In the Excludes table, add Customer Name to a higher grouping.
Drag Customer Name up to groupings.
Drag the Customer Name grouping above the Order Number Grouping.
Create and Include/Exclude calculation at the Customer Name Grouping Level.
Have the value be 1.
Step 3: Compare the Include and Exclude List
1. Add a new table element that is sourced from a join.
2. Select the Customer Level Include List from the Page Elements option as a source.
3. Select the Customer Name for the grouping level.
4. Add a second source
5. Select the Customer Level Exclude list from Page Elements.
6. Select the Customer Name for the grouping level.
7. Use a Left outer Join, grouped on Customer Name.
Step 4: Filter the Completed List
So far, we’ve created a list of customer names that we want to include and we’ve created a list of order numbers that we want to exclude. Now we want to tell Sigma to ignore the order numbers from the Include list that are also on the Exclude list.
Add a filter on the Includes/Excludes from the Exclude table where the order number is null.
(This is the field where the values are 1).
(Changing the filter to a list value makes this easier).
Since we used a left outer join we’re telling Sigma only to include the Customers from the include list that are not also on the exclude list.
Closing
As you can see, Sigma offers a flexible modeling solution that can handle complex filter criteria without requiring difficult nested calculations. In this blog, we covered two scenarios that require identifying specific combinations of items in retail orders and customers who have made those orders. This framework can be expanded to answer almost any combination of “does this line of data meet this criteria, but also this other related or unrelated criteria?” that you can ask of your data.
Data-driven users should constantly be exploring and asking for new things from their data. For organizations looking to continue maturing their data journey, it’s important to be equipped to handle anything and everything users want to throw against their data. By using this unique and flexible solution in Sigma, especially when combined with an equally unique, flexible, and powerful data warehouse like Snowflake Data Cloud, organizations can be prepared to address any complexities the future will hold.
FAQs
What if I want to see the order details of these filtered lists?
What if I have multiple include and exclude criteria?
What if I only want to include or exclude and not both?
This content was originally posted on phData’s website. Click here to read the original post