more complicated excel charting (maybe pivot tables)

[eo]

Hi,

I am currently running a Dashboard project (the technology is not Excel), but need to create chart examples of what I want developed and hoping I can create my examples in Excel so no idea where else it could be done:

I want to create charts that shows 3 measures by year in a stacked bar chart, the stacked bar components must show geography. There are therefore 3 measures and 2 variables.

A sample dataset might help:

Year Geography  Val1  Val2  Val3
2003 Africa     100   50    1000
2003 Europe     300   2000  5000
2003 America    600   12000 9000
2004 Africa     20    60    10000
2004 Europe     1300  1000  15000
2004 America    6000  2000  19000

The chart must have two sections on the x-axis - one for 2003 and one for 2004
The chart must have 3 bars in each section - one for Val1 with a total of 1,000 (100+300+600), one for Val2 with a total of 1,450 and one for Val3 with a total of 15,000
Each bar must be a stacked bar divided with sections for Geography (for example Val1 bar for 2003 divided into 100 (of a total of 1000) for Africa, 300 (of a total of 1000) for Europe, and 600 (of a total of 1000) for America

Then a similar concept for 2004, etc

I know this is a mouth full, but really need to get this right soonest. I am not sure if a pivot table must be used as a basis, but cannot even get that right?? For one how do I get each value total by year to become a bar in its own right?

EO
Hertfordshire, England

 

[xlhelp]

This one is fairly easy, if I understand what you mean. Just arrange your data as follows and plot your stacked chart.

[tt]
Africa Europe America
2003 Val1 100 300 600
Val2 50 2000 12000
Val3 1000 5000 9000
2004 Val1 20 1300 6000
Val2 60 1000 2000
Val3 10000 15000 19000
[/tt]


Canadian eh! Check out the new social forum Tek-Tips in Canada.

 

[eo]

cool, but how to arrange this sort of data where I have 1000's of rows with many real geographies and more like 6 values?

EO
Hertfordshire, England

 

[xlhelp]

May be fair amount of work, but Tranpose function is your friend in this case. Or you could try and get the pivot to look like the data.

Canadian eh! Check out the new social forum Tek-Tips in Canada.

 

[eo]

A related question:

In my pivot table to facilitatethe chart I now have sum values. I want to create a calculated value using the summed values in the piviot table and display that on the chart. I know how to create a calculated value, but it does not display the correct results.

for example the CalcVal1 = sum(Val1)/ Sum(Val2)
it does not do this, it seems to work everything out at the granular level and then summing this values up.

things in OLAP cubes using MDX was so much easier, but alas I do not have that luxury anymore

any ideas on how I can do this in the pivot table??

EO
Hertfordshire, England

 

[xlhelp]

Skip is our goldmine of knowledge as far as Pivots go (and other things too). Hopefully he will respond soon.

Canadian eh! Check out the new social forum Tek-Tips in Canada.

 

[eo]

Ah it seems as if I had a moment relapse of reason

20000/600000 = 0.033333

This showed as 0 in the pivot summary field, convert the field to % and voila = 3.33%

now how to get that sum field across the full interactive pivot showed as a %

skip??

EO
Hertfordshire, England

 

[xlbo]

showed as % of what? I assume you mean the entire row?

To show as % of row, right click on the field, choose field Settings, Options, show data as: choose % of row

You can do the same for column if that is what you want to do or % of total...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[eo]

Hi Geoff,

I will try and explain by example. I will attempt to attach a screenprint of the pivot table, so if you see it then

The original query in this thread required my to create charts that shows multiple measures by year in a stacked bar chart, the stacked bar components must show geography. I managed to do this now, it shows [GN Written Prem Conv CMNT] and [Gross Inc Clm Conv CMNT], one bar each per [Risk YOA] stacked by [Geography] South America and Asia. On the screenprint this is shown highlighted in Yellow).

One calculation calculates a [LR] value which represents [Gross Inc Clm Conv CMNT]/ [GN Written Prem Conv CMNT]. These are percentages (On the screenprint this is shown highlighted in Green) must also be shown in the chart, but as a line chart and only the total values.

Am I expecting the Pivot table/ chart to do too much??

Et

----on hind side our system does not seem to allow such attachements in this way , I hope the obove gioves you enough to go on



EO
Hertfordshire, England

 

[xlbo]

Can't see the attachment :-(


From what I understand, you currently have the LR value for each element of geography but you need it to only be for the total?


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[eo]

Thats correct, the data in the first posting...

Year Geography  Val1  Val2  Val3
2003 Africa     100   50    1000
2003 Europe     300   2000  5000
2003 America    600   12000 9000
2004 Africa     20    60    10000
2004 Europe     1300  1000  15000
2004 America    6000  2000  19000

...would now look like this (post pivot table), and LR (val1/Val2)

          Europe  America  Africa Total
2003 Val1 300     600      100    1000
     Val2 2000    12000    50     14050
     Val3 5000    9000     1000   15000
     LR   15%     5%       200%   7%
2004 Val1 1300    6000     20     7320
     Val2 1000    2000     60     3060
     Val3 15000   19000    10000  44000
     LR   130%    300%     33%    239%

...So, my chart should show the following:
Two main sections on the X-axis, one for 2003 and one for 2004, and three Stacked bar charts for each year (Val1 total 1000 for 2003 and devided into 300 for Europe, 600 for America and 100 for Africa), etc
And a line chart for LR using the secondary axis provided for by Excel, but ignoring the geography split, but only looking at the total LR %es one per year

I hope this is now clearer?

E

EO
Hertfordshire, England

 

[SkipVought]

Hi,

My FIRST order of business would be to NORMALIZE your data table using faq68-5287.

HOWEVER, before doing that, you must concatenate your ROW values into ONE column like this...
[tt]
YrGeo Val1 Val2 Val3
2003Africa 100 50 1000
2003Europe 300 2000 5000
2003America 600 12000 9000
2004Africa 20 60 10000
2004Europe 1300 1000 15000
2004America 6000 2000 19000
[/tt]
THEN do the NORMALIZATION as per the FAQ.

The result will be...
[tt]
Row Column Value
2003Africa Val1 100
2003Africa Val2 50
2003Africa Val3 1000
2003Europe Val1 300
2003Europe Val2 2000
2003Europe Val3 5000
2003America Val1 600
2003America Val2 12000
2003America Val3 9000
2004Africa Val1 20
2004Africa Val2 60
2004Africa Val3 10000
2004Europe Val1 1300
2004Europe Val2 1000
2004Europe Val3 15000
2004America Val1 6000
2004America Val2 2000
2004America Val3 19000
[/tt]
INSERT a COLUMN in column B

Parse the Year and Geo using Data > Text to colums -- FIXED WIDTH.

New the table looks like this WITH NEW HEADINGS...
[tt]
Row Geo Column Value
2003 Africa Val1 100
2003 Africa Val2 50
2003 Africa Val3 1000
2003 Europe Val1 300
2003 Europe Val2 2000
2003 Europe Val3 5000
2003 America Val1 600
2003 America Val2 12000
2003 America Val3 9000
2004 Africa Val1 20
2004 Africa Val2 60
2004 Africa Val3 10000
2004 Europe Val1 1300
2004 Europe Val2 1000
2004 Europe Val3 15000
2004 America Val1 6000
2004 America Val2 2000
2004 America Val3 19000
[/tt]
NOW you have NORMALIZED data. This will be MUCH MUCH easier to manipulate in Excel.

Using NAMED RANGES, you can SUM the VALUE for Year & Geo like this...
[tt]
=SUMPRODUCT((Year=2004)*(Geo="Europe")*(Value))
[/tt]
I'd use cell references rather than literals for Year & Geo values.

This should get you well on your way, if I understand your requirements.

Personally, I would not use a PivotChart. I'd summarize using faq68-5829 and aggregation formulas to create my chart data.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}