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.
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.
This is our example data:
Image 1. The data
In the "type" filter we choose A & B and we want to get 2 (just id 1 & 2. They share A & B). C & D have just one type 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.
Image 3. Pivot
We have 3 measures here:
Give me the number of ids in the data, according to the filter selected
# Ids that have the type filter values
case when dupcount([id]) >= (count([type]), 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 type). if so, then mark it as 1 otherwise mark it as zero. The total here is what we are after.
Selected values in type
Give me the number of values selected in the type 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 type filter values:
sum([id] , case when dupcount([id]) >= (count([type]), 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.