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 analyze 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.
- 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).
- 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:
