topic Aggr per Year in Pivot in QlikView

Aggr per Year in Pivot

Thu, 20 Aug 2009 19:15:19 GMT

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

Aggr per Year in Pivot

johnw — Fri, 21 Aug 2009 03:34:11 GMT

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.

Aggr per Year in Pivot

Fri, 21 Aug 2009 14:32:40 GMT

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?

Aggr per Year in Pivot

johnw — Sat, 22 Aug 2009 01:48:07 GMT

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

Aggr per Year in Pivot

Sat, 22 Aug 2009 02:15:02 GMT

Hi John,

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

Aggr per Year in Pivot

Mon, 23 Nov 2009 07:02:20 GMT

Excellent examples!

Aggr per Year in Pivot

Mon, 07 Feb 2011 17:32:19 GMT

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