Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hope the attached file help u
Hi,
See the attached sample might it will helps to create a pivot table.
Regards
Anand
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
Sorry Volume and Time to complete as an Expression as they are BOTH calculated
Thanks
did u want like this
see attached
thanks
Sunil
Hi,
Please check the sample i think it will helps you.
Regards,
Anand
see the attached for accurate result
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