topic Pivot Tables in QlikView

Pivot Tables

Tue, 19 Aug 2014 19:28:38 GMT

Can anyone tell me how to achieve this?

I have a straight chart like this right now. 'Week' and 'Product' are dimensions while 'City' 'Type' and 'Orders' are expressions using SUM(field).

Week	Product	State	City	Type	Orders
Week 1	1A	California	Fremont	Plastic	40
Week 1	1A	California	Fremont	Plastic	50
Week 2	1A	California	Fremont	Paper	50
Week 2	2B	Arizona	Phoenix	Paper	100
Week 2	2B	Arizona	Phoenix	Paper	60
Total					300

Is there any way to show the the following without all the other information? Or would I need to create some other 'Totals' Table?

Week 1 Total for all plastic orders

Week	Product	State	City	Type	Orders
Week 1				Plastic	90

Week 1 from Fremont

Week	Product	State	City	Type	Orders
Week 1			Fremont		140

Re: Pivot Tables

Anonymous — Tue, 19 Aug 2014 19:34:34 GMT

in your expected output as you show in your post, do you need to show the columns even though they have no info?

why not use list boxes for the columns that you don't need to show in your table and use the dimensions you need to show? so in your example, why not make have City and Type (and possibly Product and State as well) as list boxes and remove them from your straight table? if you want to then make it a pivot table, all you have to do is go to properties, go to the general tab and click on pivot table in the chart type and click ok.

ps: in your example, week 1 from Fremont table should be 90 and not 140.

Re: Pivot Tables

JonnyPoole — Tue, 19 Aug 2014 19:37:15 GMT

You could try a pivot table chart with Week as the 1st dimension. For the 2nd dimension i suggest you create a new group by hitting the 'groups' button on the dimension tab. Add Type and City to the group and ensure its a cycle group. Add the new group as a 2nd dimension .

On the presentation tab, enable 'partial sums' on the group dimension.

it will give you subtotals and allow users to dynamically switch which field you are subtotalling.