I’m trying to calculate the % of customers who have had 2+ orders within the date range. I started with:
distinct_countIf({customer_id},{order_date} >= ${DateRangeStart} AND {order_date} <= ${DateRangeEnd} AND {order_number}>=2)/distinct_countIf({customer_id},{order_date} >= ${DateRangeStart} AND {order_date} <= ${DateRangeEnd})
But that gives the % of customers within the date range who have had 2+ lifetime orders, i.e. placed their order number 2, or order number 3, or order number 4, etc.
I understand that I cannot do a PRE-AGG within distinct_countIf, so I need an alternative solution that counts the customers with 2+ orders within the date range and then calculates that count to total customers in the period.
Hello @mfenton, I have tried to replicate your scenario at my end and I am trying to depict it below in step by step manner. Hope this helps! Please let me know! Thanks!
Step 1: Sample Dataset that I used:
Step 2 : Created 3 Calculated Fields - Qualifying Customer Count, Total Customer Count, % of Customers
@sagmukhe Thank you for such a thorough and easy explanation! The calculations make total sense. In your example, you have dimensions and the calculated fields populate every row in the result. My issue now is how to filter to only one row. I should have included this before, but I would like the resulting visualization to appear like this:
Hi @mfenton. Glad the solution @sagmukhe provided worked for you. In terms of the filtering, did you add the filter from the data controls to the visual?
If you’re talking about the formatting, you can use conditional formatting for that.
Yes, the filter is applied. And conditional formatting is not the issue.
The solution is grouping by the same dimensions as in the original data sample (see his screenshot) and then aggregating on Min. The calculation for the date range is correct but it is populating every row in the table result. However, I would like that value calculated and then displayed without the Group By, as in my screenshot.
