topic Re: Pivot table a few questions in QlikView

Pivot table a few questions

Thu, 25 Aug 2011 15:37:06 GMT

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

Re: Pivot table a few questions

SunilChauhan — Thu, 25 Aug 2011 15:44:47 GMT

hope the attached file help u

Re: Pivot table a few questions

its_anandrjs — Thu, 25 Aug 2011 16:02:58 GMT

Hi,

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

Regards

Anand

Pivot table a few questions

Thu, 25 Aug 2011 16:09:44 GMT

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

Pivot table a few questions

Thu, 25 Aug 2011 16:14:22 GMT

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

Thanks

Re: Pivot table a few questions

SunilChauhan — Thu, 25 Aug 2011 16:16:33 GMT

did u want like this

see attached

thanks

Sunil

Re: Pivot table a few questions

its_anandrjs — Thu, 25 Aug 2011 16:24:38 GMT

Hi,

Please check the sample i think it will helps you.

Regards,

Anand

Re: Pivot table a few questions

SunilChauhan — Thu, 25 Aug 2011 17:02:58 GMT

see the attached for accurate result

Re: Pivot table a few questions

Fri, 26 Aug 2011 08:08:52 GMT

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 ,