8 Replies Latest reply: Aug 26, 2011 4:08 AM by Anne Duffy

# Pivot table a few questions

Hi guys

Pivot Tables have never been my friend, I hope someone can help me out.

I have a QV load coming from excel, where I have a list of activities, then sub categories,the volume of Sub categories, and the time it takes to complete each of these sub categories.

I wish to create a Pivot table

That shows All the Activities, but the top 5 Sub categories, Based on time to complete, then the rating of where this activity is based on Volume.

Please see example this is the source data

 Activity Sub category Number Time to complete Shopping Grocery 10 52 Shopping Fish 100 155 Shopping Veg 15 27 Shopping Meat 22 33 Shopping Chicken 72 77 Shopping Housewares 18 85 Gardening Watering 15 5 Gardening Planting 85 10 Gardening Pruning 55 15 Gardening Triming 100 20 Gardening Hoeing 72 25 Gardening Digging 100 30

I would wish it pivot to look like the following data;

 Activity Sub category Number Time to complete Shopping Fish 1 155 Shopping Housewares 4 85 Shopping Chicken 2 77 Shopping Grocery 5 52 Shopping Meat 3 33 Gardening Digging 1 30 Gardening Hoeing 3 25 Gardening Triming 1 20 Gardening Pruning 4 15 Gardening Planting 2 10

So,

The number is now the position it held in the overall run of things

And only the highest 5 time to completes per category is displayed,

Is this possible, any help or direction would be greatly appreciated

Thanks

A

• ###### Re: Pivot table a few questions

hope the attached file help u

• ###### Pivot table a few questions

Thanks Sunil,

Im sorry I had simplified my sample data, the number is actually an Expression , I tried to follow your sample and put it in as a calculated demension, but I get error in calc, is it possible to do the same as your sample, while having the volume as an expression ?

Thanks

A

• ###### Pivot table a few questions

Sorry Volume and Time to complete as an Expression as they are BOTH calculated

Thanks

• ###### Re: Pivot table a few questions

did u want like this

see attached

thanks

Sunil

• ###### Re: Pivot table a few questions

Hi,

See the attached sample might it will helps to create a pivot table.

Regards

Anand

• ###### Re: Pivot table a few questions

Hi,

Please check the sample i think it will helps you.

Regards,

Anand

• ###### Re: Pivot table a few questions

see the attached for accurate result

• ###### Re: Pivot table a few questions

Thanks a million guys

All samples are very helpful, I am going to work through these today and Ill post back any further queries,

Thanks again ,

A