I have this Q query: “How many merchants had a mean ticket between 300k and 500k quarterly?”
For this, I have the fields:
Merchant ID
Date
Total Amount
Total Transactions
And the calculated field:
mean ticket = SUM({Total Amount}) / SUM({Total Transactions})
So, for this query I want Q to:
Get merchant’s total quarter amount and transactions
Calculate mean ticket
Filter by that aggregated mean ticket
Count unique merchants
In SQL it would be something like this:
SELECT COUNT(DISTINCT merchant_id)
FROM (
SELECT
merchant_id,
EXTRACT(YEAR FROM transaction_date) AS year,
EXTRACT(QUARTER FROM transaction_date) AS quarter,
SUM(amount) / SUM(transactions) AS mean_ticket
FROM transactions
GROUP BY merchant_id, year, quarter
) AS subquery
WHERE mean_ticket BETWEEN 300000 AND 500000;
However, Q apparently can’t recognise this complex filter and aggregation because I’m not getting any values.
Is there a way to make Q answer this kind of questions? Or is it impossible (for now) to make Q perform these two steps aggregations?
However, now it gives wrong results for questions like “monthly mean ticket”, because it tries to do sum of mean ticket by month instead of mean ticket by month
Is there a way to handle the different aggregation levels?
As you noted, the issue with this solution is that it only works if the user specifically asks about quartertly. The only workaround I can think of is to rename the calculated field as “quarterly mean ticket” and to create another one for monthly, yearly, etc.
Hi @alaresch,
Is there a better way to set up the topic so that Q can answer these types of questions?
I tested it in the “Software Sales” sample topic.
When I ask how many customers have sales less than 1000 in 2023, I expect the answer to be 8.
The answer from Q is 99 because it’s counting the number of customers who have individual orders less than 1000. It’s not aggregating the sales amount across all orders even if the default aggregation for sales in sum. Is this because Q can’t filter by measures?
I understand why we need to use sumOver and to answer this question in a dashboard, I have to create my calculated field the same way. However, in Q it means that the calculated field will only work if the reader asks about annual sales specifically and won’t work if they ask about quarterly, monthly, weekly, etc.
Building on David’s progress with our sample topic, I am able to get a correct answer without any calculated field when I phrase the question as “customers with less than 1000 in sales in 2023”
That shows me a horizontal bar chart with just the 8 customers that had a sum of 2023 sales less than 1000.
So it looks like we have some issues with fragility around specific phrasings here, but the desired behavior is technically supported. I will have our team look into it.
Diego, what you could do in the meantime is play around with some different question phrasings until you get a desired result, and then mark that result as reviewed. Then, your end users can start with your working version and plug in whatever date granularity they need.
Hope this helps and apologies for the inconvenience.
Thank you for your suggestion, @crease. Just to clarify, my original question involves the calculation of the ‘mean ticket’, which is calculated by dividing sales by transactions. As I understand it, this isn’t a straight summation problem because summing daily mean tickets isn’t equivalent to summing all sales and dividing by all transactions
