I have a dataset which contains entries for outlets. Each entry has a field ‘outlet ID’, ‘outlet name’, ‘state’, and ‘product name’ (simplified). Each outlet can have 1 (if it offers none of the products) or several entries, one additional entry for each product (with a different ‘product name’) offered.
I want to create a bar chart which shows for each filtered product a fraction - the product coverage for each product, i.e., the number of outlets which offer a certain product (numerator) divided by total outlets (denominator). This data could also later be grouped, e.g., by states, or other filters could be applied to the outlets, e.g., only outlets are taken into consideration which offer at least one product out of a set of certain products.
How can I achieve this?
As I understand it, it is not possible to create a calculated field which computes a fraction where the aggregation level in the denominator is different from the numerator. That means, when in the field well the X axis contains the field ‘product name’ and the value contains the calculated field, that calculated field to be calculated as distinct_count({outlet ID}) / distinctCountOver({outlet ID}, [], PRE_AGG)
is not possible.
I get this error message: “Mismatched aggregation. Custom aggregations can’t contain both aggregate “distinctCountOver
” and non-aggregated fields “DISTINCTCOUNTOVER(“outlet ID”, PRE_AGG)”, in any combination.”
Any help is appreciated.
P.S.: I now added min() to the denominator. distinct_count({object ID}) / min(distinctCountOver({object ID}, [], PRE_FILTER))
doesn’t throw an error, not yet sure if it works correctly.