
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count how many days are above average
Hello guys,
My model has customer, date, and quantity. In the report, I need to give a table by customer and a date filter. During the selected date span, I want to know how many days each customer bought more quantity than average.
This is the function I wrote:
sum(if(aggr(SUM(Quantity),Customer)
>=aggr(SUM(Quantity)/COUNT(Customer),Date)
,1,0))
But the output is 0 for each customer, which is not correct. Could you help me check where the problem is?
Thanks in advance!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'd generally advise to not use aggr function, as it can slow down your object loading time quite a lot and unnecessarily increase the complexity of your expression
In your script you can create two tables for sum(quantity) grouped by customer and one with sum(quantity)/count(customer) grouped by date.
If it's not possible in the script, try
if(sum(aggr(SUM(Quantity),Customer))
>=sum(aggr(SUM(Quantity)/COUNT(Customer),Date))
,1,0)

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What is "average"?
Is it just for the relevant customer? Or is it over all customers?
Should it be counted over all dates, or just the selected ones?
If it is per customer and date, within selection, you could try
Sum(
Aggr(
If(Sum(Quantity)>=Sum(total <Customer> Quantity)/Count(distinct total <Customer> Date),1,0),
Customer,
Date))
