I'm trying to create a custom field in an Indicator Widget that must be:
Count of ID,
where the Order Date is in the last 60 days
and the status is paid
and the total amount sold by this ID is greater than 2000.
I successfully created the first 2 filters using a custom formula:
(([# of unique seller_id] , [state_group]) , [Days in order_date])
But I can't filter by an aggregated field. Can I get this result without having to create a custom table?
SQL expression that gives the desired result:
FROM order_dim AS od
INNER JOIN order_detail AS o
ON o.[order_id] = od.[order_id]
WHERE od.state_group = '1. paid'
AND DayDiff(NOW(), od.[order_date]) < 60
GROUP BY od.seller_id
HAVING SUM(o.[total_amount]) > 2000
Please sign in to leave a comment.