Rate of guest intake

Question & Answer
,
1

I want to add calculated field to find rate of guest intake for the product that is
(number of guest who purchased the product / total number of guest)*100
For an example there are 3 different product type(Tea, Coffee, Soda) and 100 total guests purchased those three. Out of which 20 bought coffee. so To measure the rate of guest intake for a coffee would be:
(20/100)*100
what I did is distinct_count(total_guests)/disintc_count(total_guests,[{product_name}]) and visual level filter set to product_name = ‘coffee’. But it filtered our total_guest count as well.
I am unable to apply Level Aware Calculation filed here due to lack of my undertesting.

2

Hello @dna, I would suggest utilizing the distinctCountOver function. For your total guests we will leave the partition field empty. It will look something like this:
distinctCountOver({total_guests}, [], PRE_AGG)/distinctCountOver({total_guests}, [{product_name}], PRE_AGG)

That should give you the solution you are looking for!

1 Like
3

isnt it other way around??
(distinctCountOver({total_guests}, [{product_name}], PRE_AGG)/distinctCountOver({total)guests}, , PRE_AGG))*100
Here i am not getting as expected output. I think it is counting all the distinct records. What I want is count of distinct customer_id(total_guests) /count of distinct customer_id who purchased the product. need to avoid duplicates with same customer_id with same product_name.

1 Like
4

Hello @dna, you are correct, I typed out the equation backwards. You would divide the distinct count of customers who purchased the product over the total number of distinct customer_ids.

The function would look like this:
distinctCountOver({customer_id}, [{product_name}], PRE_AGG)/distinctCountOver({customer_id}, [], PRE_AGG)

If you wanted the aggregation of which product_name to be decided by a filter, you could also create a product_name parameter, create a control so the user could select which product would filter the visual, and replace your product_name field in the function with the ${product_name} parameter.

You also do not need to multiply the number by 100. You can format the field within the field well of the visual to display it as a Percent.

5

@DylanM Thanks for the response. But this is not giving me the expected output and returns 1 as an outptut. I use this formula:
distinctCountOver({customer_id}, [${product_name}], PRE_AGG)/distinctCountOver({customer_id}, , PRE_AGG)
But Its giving me and error: Please correct the distinctCountOver to use PRE_AGG or PRE_FILTER levels and partiton only by column references.
Unfortunately I am unable to use parameter here as in groupby.
What i want to achieve is User would be able to filter this metric on different product_name and also rest_no.
So what i want is: percentage of (total distinct guest purchased product_name/ total distinct guest purchased any product_name).
End user would be able to filter this metric for any product_name and for any rest_no.
That is if user select coffee as a product_name and rest_no as ‘101’. The metric would be
total distinct guest purchased coffee at rest_no. 101/ total distinct guest purchased any product_name at rest_no 101.

6

Hello @dna, what if you tried an ifelse statement to return your customer_id based on your parameters and then removed the filtering on the visual.

customerIdByFilter = ifelse(${product_name} = {product_name} AND ${rest_no} = {rest_no}, {customer_id}, NULL)

Then your calculated field that creates the percentage would be this:
distinct_count({customerIdByFilter})/distinct_count({customer_id})

To make sure your field is calculating as expected, you can add a filter on the visual for customerIdByFilter and set it to exclude NULLs. One other thing to note, this will require your parameters to be single value parameters (not multi-select) and they will need a default value.