Working with Pivot Charts

[#1]

Hello Gurus,

Hope its a simple question?

I have some data in Excel sheet, where I have clients X, Y, Z who made purchases in Jan and Feb for various amounts, I want to show a Pivot chart, where I need to calculate the average purchases made by each client and display in the chart, and the totals of that expression at the bottom,,, Checked the forums, I think people have already posted the same issue but didnt find a suitable answer,

Also the problem is when I am calculating the average in a Pivot chart for the clients I want the clients to display the total monthly average value like below

THIS IS WHAT I GET:

Jan A 1000

Jan B 2000

Jan C 3000 (It shows like this as its calculating the averages at the column level for each client)

THIS IS WHAT I WANT TO SEE:

But I have a monthly averge of say 500 I want to see the result as below

Jan A 500

Jan B 500

Jan C 500

Can this be achieved with an expression or we need to join it when we load the data?? I somehow worked out the column totals using a Straight table, but want it to work in Pivot chart for many reasons

Thanks in Advance,

Mady

[#2]

OOps a small correction, the Client names is ABC instead of XYZ in the above post

[johnw]

What does your DATA look like? It's hard to give you an expression to calculate something from your raw data when we don't know what your raw data looks like.

[#4]

Hello John, Thank you for the quick reply, my Data looks like below in the excel sheet Date	Company	Value
1/31/2010	A	1500
1/31/2010	A	500
1/31/2010	B	2000
1/31/2010	C	1000
1/31/2010	A	1000
2/28/2010	A	10000
2/28/2010	B	3000
2/28/2010	B	4000
2/28/2010	C	1000

[#5]

Hello Gurus,

Anyone to take up this issue and help please?

Thanks in Advance,

Mady

[johnw]

And how do you get 500 for A, B and C for this data? Or would this data have some other total? You mention a monthly average, so do you just want the monthly average for each company? So Jan A = (1500 + 500 + 1000)/3 = 1000? And you want to see those averages like this?

Month Company Avg
Jan A 1000
Jan B 2000
Jan C 1000
Feb A 10000
Feb B 3500
Feb C 1000

You'd do that like this:

dimension 1 = Month
dimension 2 = Company
expression 1 = avg(Value)

[#7]

Hello John,

Thank you for the reply. You are right, thats what it shows when me. Well as I told before, my data looks similar to what is there here. And I have been using 2 dimensions and a expression to calculate the average.

My question is, when we are calculating a monthly average i.e., say we SUM up all the sales and divide them by 3 and the value is average result for that month. Say I get 1000 I want to see that monthly avergage value across all the colums and not their respective values.

In short, I want to see the monthly average for all the rows under the Avg column and at the bottom I need a total of the rows within the Pivot chart.

Hope this would give more detailed description of what I am looking at

Mady

[johnw · Accepted solution]

So you want an average of the sum for each customer for each month? Jan = (1500+500+2000+1000+1000)/3 = 2000, Feb = (10000+3000+4000+1000)/3 = 6000? Then sum up the rows to get 24000? Like this?

Month Company Avg
Jan A 2000
Jan B 2000
Jan C 2000
Feb A 6000
Feb B 6000
Feb C 6000
Total 24000

If so, there might be a simpler expression, but this works:

sum(aggr(avg(total <Date> aggr(sum(Value),Date,Company)),Date,Company))

See attached.

If that's not it, can you post the chart you DO want to see? And in the future, posting both example data AND the chart you'd like to see for that example data can speed things up. Bonus points for posting a QVW with an inline load of that example data so that people can verify their solution works without needing to build a QVW from scratch.

[#9]

Thank you John, Thats exactly what I was looking at

Mady

[#10]

I am trying ti accomplish the attached chart in Qlikview(Report.png) .  So far I was able to Get the Attached chart in the Pivot Table with First Dimension as (Claim Type Code) and Second one is a Calculated Dimension  (=ValueList('Sum','AVG') ).

The problem is that I cant get the Totals of every thing like in the Chart  (QV_Result.png)

Any suggestions.