Pie Charts from Multiple Columns

[#1]

Hi all,

I have 10 columns which have an 'hours' figure in them (as in hours of audio)

I'd like to create a pie chart that shows the sum of all the rows in each column as a percentage but i'm struggling to achieve this! I have added all 10 columns as dimensions, but what should my expression be??

[swuehl]

Not sure if I fully understand your table structure, could you post some lines of sample data togehter with your expected result?

If I had a kind of crosstable with several fields containing the same type of data, I would start with a crosstable load (please check the help for crosstable load prefix) to convert the table into a straight table:

Crosstable (Type, Hours) LOAD recno(), * INLINE [

Audio, Video, News, Work

2, 1, 3, 4

0,2,3,1

4,1,3,4

];

Just used four fields to demonstrate...

The create a pie chart with dimension Type and as expression

sum(Hours)

and check the relative option?

[#3]

Thanks for the reply, sadly i can't really post the data file but it is a table of:

Year (Derived from Date on Reload), Date (yyyy-mm-dd), Hours1, Hours2, Hours3, Hours4, Hours5, Hours6, Hours7, Hours8, Hours9, Hours10

Ideally i want a pie chart saying for a year Hours1 was 20%, Hours2 was 10%, Hours3 was 5% etc.

- can isomehow use a crostableforthat?

[swuehl]

Yes, should work, you just need to take care of the number of qualifying fields preceding your HourX fields (to in this example, Year and Date), so maybe like

CROSSTABLE (Hour#, Value, 2) LOAD Year, Date, Hours1, Hours2, Hours3, Hours4, Hours5, Hours6, Hours7, Hours8, Hours9, Hours10 from Table;

[#5]

Thanks, So does this effectively just create a new table with rows that are the value of my hours fields? Will i still be able to label the slices of the pie appropriately (hours1,hours2,hours3.. etc.)?

[swuehl · Accepted solution]

The Crosstable will essentially convert something like

ID, Hours1, Hours2,Hours3

1, 1, 2,5

2, 3, 4,6

to

ID, Type, Value

1, Hours1,1

1, Hours2,2

1,Hours3,5

2, Hours1,3

2, Hours1,4

2, Hours3,6

So Type is a new field with all your labels.

[#7]

Amazing, many thanks

[#8]

Ah sorry i've oversimplified, so instead of being called 'hours1,hours2,hours3' the fields are called things like 'BobsPlayer, JimsSystem, GeorgeHome' - for example - is this example going to knacker me if i'm not using Hour<int> naming?

Also presumably i don't have to worry about the date field since that can be done with listbox filtering?

[swuehl]

The Names of your fields that are going to be transferred into the Type field (you can of course name your Type field as  you like, just give the name in the CROSSTABLE arguments) are absolutely arbitrary. The cross table load will eat all fields that follow the qualifying fields (so you need to take care on the order you are loading your fields!).

Your date field as a qualifying field will not be transformed, but the relation to the TYPE and Values will still exist, so a filtering will no problem. Or have I misunderstood you here?

[#10]

Nope, I got it while you were replying! Sorry my fault - that's great, cheers - the pie looks awesome