2010 Branching Choice Columns

Question

I have a dilemma with a list I have that I wish to export to excel and create pivot charts/graphs for...

Currently my list has several fields that I wish to consolidate into one.

I have: - Team Name - January work demand planned - January work demand Actual - January overtime demand planned - January overtime demand Actual

And this repeats for the 12 months.

All these columns are Choice columns, with the choices: Hot, Cold, Within Tolerance.

Now thanks to all these fields, my data is very convoluted, and doesn't work well when trying to display them specifically with a pivot chart.

A solution I thought would work would be so merge these fields into one. IE. January would be the column name, and then for each of the 4 fields under them (actual demand, planned demand etc.) I could individually put Hot/Cold/Within Tolerance.

Would this be possible first of all? and if so how would I be able to do this.

Also: Another challenge I've faced is that pivot tables and charts especially really like numbers. So on my current table I've made use of calculated columns to display a field saying "Hot" to equal "3" and then measured it this way. Would my idea of a solution be able to implement this logic so that it is Pivot Chart-friendly?

Thanks

Answer 1

If you don't already have a lot of data in the list, I would make it 6 columns total

1. Team Name
2. Month
3. work demand planned
4. work demand Actual
5. overtime demand planned
6. overtime demand Actual

You will have to enter an item for each month for each team but it will make pivoting with that data much easier when you export it into excel.