How to divide weekly sales by annual customers?

Q&A
, ,
1

Our spice dataset is organized by transactions. I need to calculate the total count of transactions each week / the total number of distinct customers for all time (subject to filters like {region} etc. If I do something like:distinct_count({transaction_id)) / distinct_count({customer_id}) and then plot it on a chart by week, the denominator becomes the count of customers who had a transaction that week, not all customers across time.

I can get total customers across the time period with a formula like:
distinctCountOver({customer_id},[{cust_constant}], PRE_AGG), but if I try: distinct_count({transaction_id)) / distinctCountOver({customer_id},[{cust_constant}], PRE_AGG) I get error “mismatched aggregation.”

Given a filtered group of transactions over a time period, how can I get the total customers across the time period as a denominator?

2

Hello @aswilson!

Depending on the value of your cust_constant field you may be able to turn this into an ifelse statement.

For example, you could say something like

ifelse(
{cust_constant}  = String/Boolean, 
distinct_count({transaction_id)) / distinct_count({customer_id}), 
0)

Really what I am doing here is trying get the partition field from your distinct count over function into this ifelse statement but depening on the value it may not work.

3

Thank you for the response. Actually, {cust_constant} is a hack. It’s a calculated field attached to every record whose value is simply “foo”. In order to count distinct customers PRE-AGG I have to have a grouping field. QS won’t let me distinctCountOver({customer_id}, PRE-AGG).
My challenge is getting the total count of all customers who purchased over a year and using that value as the denominator for sales per [all] customer each week.
If I aggregate a chart by week, QS seems to automatically aggregate my customers to only those who made a transaction that week. I need something like “distinct sales per week divided by total distinct customers that year”

4

Hey @aswilson !

Could you try something like:

distinct_countOver({transaction_id}, [], PRE_AGG) / distinct_countOver({customer_id},[{cust_constant}], PRE_AGG)

You may need to partition by something else but this I believe is in the right direction.

1 Like
5

Thanks! That led me to the solution.

  1. I didn’t realize I could group by ‘[ ]’ so I got rid of the useless {cust_constant}.
  2. My real issue was a mismatched aggregation because I needed a distinct_countIf for the transactions (to get specified ones), but I needed to divide by the distinctCountOver for the customers to get all across the whole time period.
  3. Fix was to min(distinctCountOver(…)
  4. The final calc that works is :slight_smile:
    distinct_countIf({transaction_id}, {if expression}) / min(distinctCountOver({customer_id},[],PRE_AGG))
1 Like