Aggr per Year in Pivot

[#1]

Hi,

customer table:

Customer:
LOAD * INLINE [
Customer_ID, Sales, Year
1, 10, 2009
2, 20, 2009
2, 15, 2009
3, 10, 2009
4, 40, 2008
5, 50, 2008
5, 10, 2009
];

I currently use avg(total aggr(sum(Sales), Customer_ID)) to calculate the average.

unfortunately this is working only for the whole data because of total.

What I' trying to show is the average overall Customers per year.

2009: average is 13
2008: average is 45

Thanks.

Aloah

[johnw]

For your example data, a Year dimension with avg(Sales) expression produces the numbers you want. I'm guessing that you need something more complicated than that, but your example data doesn't need it.

[#3]

Hi John,

I'm using two dimensions (Customer_ID and Year). With avg(sales) I'dont get the avg over all customers per year. Or did I get it wrong?

[johnw]

You said:

"What I'm trying to show is the average overall Customers per year.

2009: average is 13
2008: average is 45"

From that, I assumed that that was what you wanted to see. That's a chart with ONE dimension, Year, and one expression, avg(Sales). I guess that wasn't what you wanted.

So again guessing what you want, are you trying to compare the sales of EACH customer with some sort of average across customers for the year? That would make sense. So do you want this?

Year Customer_ID Sales Average
2008 4 40 45
2008 5 50 45
2009 1 10 13
2009 2 35 13
2009 3 10 13
2009 5 10 13

If that's what you meant, the Average column would be this:

avg(total <Year> Sales)

However, now I'm not so sure that that's what you want either. Since you have two different batches of sales for Customer 2, wouldn't you want your average to be (10 + 35 + 10 + 10) / 4 = 16.25 instead of (10 + 20 + 15 + 10 + 10) / 5 = 13? Don't you want this?

Year Customer_ID Sales Average
2008 4 40 45
2008 5 50 45
2009 1 10 16.25
2009 2 35 16.25
2009 3 10 16.25
2009 5 10 16.25

If that's what you want, the Average column would be this:

avg(total <Year> aggr(sum(Sales),Year,Customer_ID))

[#5]

Hi John,

wow. thanks for help. Sorry for my ambiguous description of what I was looking for.

[#6]

Excellent examples!

[#7]

Hello, how would you represent those data on the same graph, both:

- on X axe: time (here year)

- on Y: several curves showing

- the sales for each customer

- the average for each year

I am trying to find a solution on this kind of topic without success

Thanks you for your help,

Bruno