Count how many days are above average

[wanyunyang]

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!

[Almen]

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)

[hic]

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))