topic Re: Customized subtotals in QlikView

Customized subtotals

Thu, 11 Feb 2016 10:02:32 GMT

Hi ,

I d like to calculate subtotals in a pivot table, but i need customized subtotals, i have a dimension that contains products , id like to have a pivot table like this:

Products	2013	2014
SHOES 1	10	11
SHOES 2	15	20
TOTAL SHOES	25	31
JACKET 1	20	12
JACKET 2	5	8
TOTAL JACKETS	25	20

the option "show partiel sums " give me totals by the content of all the dimension, i need subtotals by a specific elements (depending on the needs) from a dimension.

Thank you

Re: Customized subtotals

sunny_talwar — Thu, 11 Feb 2016 11:39:46 GMT

Not sure how your data is structured, but if you have Category before product, you might be able to make 'Show Partial Sum' work for you

Category, Product, Value

SHOE, SHOES1, ...

SHOE, SHOES2, ...

JACKET, JACKET 1....

add both Category and Product as dimension and use partial sum

Re: Customized subtotals

awhitfield — Thu, 11 Feb 2016 11:53:48 GMT

Hi there,

see the attached simple example:

HTH

Andy

Re: Customized subtotals

Thu, 11 Feb 2016 11:56:48 GMT

thank you for your reply.

If I add a category the result wil be like this:

Re: Customized subtotals

MayilVahanan — Thu, 11 Feb 2016 12:13:55 GMT

Try like this

Data:

Load *, Capitalize('Total ' & SubField(Product,' ', 1)) AS Category Inline

[

Product, qty, year

Shoes 1, 10, 2013

Shoes 1, 11, 2014

Shoes 2, 15, 2013

Shoes 2, 20, 2014

jacket 1, 20, 2013

jacket 1, 12, 2014

jacket 2, 5, 2013

jacket 2, 8, 2014

];

Pivot Table

Re: Customized subtotals

Thu, 11 Feb 2016 12:27:43 GMT

thank you all for your answers.

MayilVahanan could you please explain to me the instruction

Capitalize('Total ' & SubField(Product,' ', 1)) AS Category

Re: Customized subtotals

MayilVahanan — Fri, 12 Feb 2016 04:36:50 GMT

Creating the Category based on the values in Product.

SubField(Product,' ', 1) <- Based on your example, I hope that, your products are look like SHOE 1, SHOE 2..

In that, SHOE is category, we can able to fetch the SHOE word alone with the help of Subfield.

And Adding a string 'Total ' in front of category to form 'Total SHOE'.

Captialize is used to form the final value like 'Total Shoe'.

Hope you wil clear now.,