topic Re: Subtotal on dimension in pivot table in QlikView

Subtotal on dimension in pivot table

Wed, 15 Aug 2012 07:17:17 GMT

I have the following script:

Product:
LOAD * INLINE [
    Product, Invoiced quantity
    'Chair', '10'
    'Table', '20'
    'Closet', '15'
];

Material:
LOAD * INLINE [
    Product, Material, Used quantity, Uom
    'Chair', 'Wood', '1,0', 'm3'
    'Chair', 'Fabric', '0,5', 'm2'
    'Table', 'Steel', '0,2', 'kg'
    'Closet', 'Wood', '2,0', 'm3'
    'Closet', 'Plastic', '0,7', 'kg'
];

With this I have created a pivot table that looks like this:

This table allows users to see what materials are used in a product and how much of it is used for the invoiced quantity.

I have added the Invoiced quantity as a dimension because I do not want it to repeat within each fabric.

What is still missing from this table is a total row. I know how to add this using the "Show partial sum" option in the Presentation tab of the table. But this will only give me totals of the expressions. I would also like a total of the Invoiced quantity, which is a dimension.

The following pivot table does have a total for each Used quantity, but not for the Invoiced quantity:

Is there a way to get the total of the Invoiced quantity on the total line as well?

The attached file Pivot_total.qvw contains the tables and data used in my question.

Re: Subtotal on dimension in pivot table

SunilChauhan — Wed, 15 Aug 2012 09:03:28 GMT

covert you pivot to straight table first

then select invoice expression> Express total or sum of row

select it

then again convert it into pivot

hope this helps

Re: Subtotal on dimension in pivot table

Wed, 15 Aug 2012 09:27:06 GMT

Thanks for the response Sunil.

I don't think the solution you are suggesting can work, because my Invoiced quantity is not an expression, but a dimension.

The only reason that I choose it to be a dimension is because I do not want the column repeating itself with each Material.

Re: Subtotal on dimension in pivot table

SunilChauhan — Wed, 15 Aug 2012 09:32:53 GMT

one more way you can try

aggr(if (dimensionality()=0,sum(Invoice Quantity),Invoice Quantity),Product) in dimension

hope this helps

Re: Subtotal on dimension in pivot table

Wed, 15 Aug 2012 10:01:45 GMT

That would add up the Invoiced quantities if there are multiple lines in the Product table of the same product. But this will not give me a total line.

I would like to have a total of all of the Invoiced quantities. This column is only a dimension because I only want it to appear ones. It should not repeat itself like the Used quantity does.

Thanks

Re: Subtotal on dimension in pivot table

jonathandienst — Wed, 15 Aug 2012 11:02:32 GMT

I don't think that it is possible for you to do it exactly that way. As a workaround, perhaps you could consider a separate text box showing the invoiced quantity total.

Regards

Jonathan

Re: Subtotal on dimension in pivot table

Wed, 15 Aug 2012 11:31:17 GMT

That's what I was afraid of.

The workaround that you suggest is a good idea. Since my total is on the top I can make it look like the textbox is part of the table.

Thanks!