Pivot table a few questions

[#1]

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

[SunilChauhan]

hope the attached file help u

[its_anandrjs]

Hi,

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

Regards

Anand

[#4]

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

[#5]

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

Thanks

[SunilChauhan]

did u want like this

see attached

thanks

Sunil

[its_anandrjs]

Hi,

Please check the sample i think it will helps you.

Regards,

Anand

[SunilChauhan · Accepted solution]

see the attached for accurate result

[#9]

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