Conditional Filtering on Measured Values

[intapiuser]

 Analytical Need 

Aggregation functions are used to group a set of values to perform calculations that return a single value. SUM(), MEDIAN(), and COUNT() functions are some of the most common aggregation functions examples. Simple aggregation functions require only a numeric field to perform the aggregation upon: Function (<Numeric Field>). 

Multi-pass is a type of an aggregation function which performs an additional calculation over the data and group it by a specific field: Function (<Group By>, <Aggregation>). More information about using multi-pass can be found on this link.

Certain business needs, such as performing conditional calculation by group in a pivot table while keeping and allowing flexibility in the front end might require more advanced calculations. Therefore, we would like to do the calculation on the dashboard level rather than in the ElastiCube.

 Modeling Challenge

How do we perform grouping in the dashboard while keeping the ElastiCube flexibility

Let's assume that a Sales Manager would like to answer the following business question:

Restaurants are sorted into 3 categorizes by number of customer visits; 0-100 customers, 100-200 customer or 200+ customers. What is the total # of customer visits each category of restaurant receives?  

Solution

First, it is crucial to break down the question into components:

• Count # total customers.
• Group by restaurants.
• Filter by groups.

As there are several filters over the same value, instead of creating a filter on a widget level, we will create a filter over the measured value for each group.

Let's start by grouping our values and creating a new predefined value for each group. In our example, Group 1:0-100 customers, Group 2: 100-200 customers, and so on. Then we will use multi-pass aggregation to calculate the specific group by the relevant filter.

Let's break it down to understand how to create the correct formula that fits for this scenario:
1) To answer the need, we will use a CASE WHEN statement to create a range of # customerID to be between 0-100 customers which we would like to eventually sum. We would like to start with examining the filter first, to make sure we are filtering out the correct data. To do so, we will add a new value and create a formula by pressing on 'fx'. We will add the following formula:

Essentially, return the # customers, if the # customers is in the predefined range, else return 0.

To verify the correctness of the filter, let us verify the data:

We can see that when the total # of customers of a restaurant are within our filter's range, the total is added to the 'Formula' column and when it isn't in range, the column will get the value of 0.

2) The next step will be to add the multi-pass function in order to group the results by Restaurant and sum all results. This will sum all the values already filtered for all restaurants, including ones that received a value of 0 from the initial filter. This will lead to the correct result which is summing up only the # customers for 0-100 restaurants. Here's the final query:

 

Here's the pivot now:

As the grouping duplicates the data for each row, we would remove the RestaurantID row, and get the final result of the data:

3) Apply the same logic with only changing the range for the rest of the group in a different measured value.

Summary:

The CASE WHEN statement performs the filter into groups and for every RestaurantID that its total amount of unique CustomerID is between 0-100, we return the # of unique customers. If the total amount is not in range, we count nothing.