AND filter - How to identify Items that have all selected values

[intapiuser]

 Analytical Need 

I would like to count all the products that are sold in Germany & Canada. When you select Germany and Canada in a typical Sisense filter, it will give you results for products that are sold either in Germany or in Canada but not sold only in both countries.

 Modeling Challenge

In order to achieve this, you may need to perform a self join in the elasticube. If you have several combinations of attributes that you want analyse then you need to have many many tables which is not scalable or maintainable. 

The preferred way to do it is in the dashboard.

Solution

This is our example data:

Image 1. The data

In the "country" filter we choose Germany & Canada and we want to get 2 (just id 1 & 2. They share Germany & Canada). 3 & 4 have just one of the countries and we don't want to count them.

Image 2. Filter Selection

We present here 2 widget types that have the formula to calculate it but you can take the formula and apply it to other widget types.

1. Pivot:

Image 3. Pivot

We have 3 measures here:

# Records 

 dupcount([id]) 

Give me the number of ids in the data, according to the filter selected

# Ids that have the Country filter values 

case when dupcount([id]) >= (count([Country]), all([id])) then 1 else 0 end

Here we check whether the number of ids is equal or higher than the amount of values selected in the filter (we add the all(id) because we don't want the rows to affect the count of Country). if so, then mark it as 1 otherwise mark it as zero.

Selected values in Country

 (count([Country]), all([id])) 

Give me the number of values selected in the Country filter, regardless of the ids (in the pivot it automatically breaks it down by the rows and here we want to ignore it).

2. Indicator: (this is the formula you should use for other widget types)

Image 4. Indicator

# Ids that have the Country filter values:

sum([id] , case when dupcount([id]) >=(count([Country]), all([id])) then 1 else 0 end)

This is the same as the formula in the pivot, just with a multipass aggregation over all ids. This is because we want to sum up all the 1s for each id.

Download:

Dashboard

Ecdata