Customized subtotals

[#1]

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

[sunny_talwar]

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

[awhitfield]

Hi there,

see the attached simple example:

HTH

Andy

[#4]

thank you for your reply.

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

Category	Products	2014
SHOES	SHOES1	10
	SHOES2	11
JACKETS	JACKETS1	15
	JACKETS2	5

howerver the final user needs to see all those informartion(category and products) in the same columns like the board in my first post.

[MayilVahanan]

Hi

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

[#6]

thank you all for your answers.

MayilVahanan could you please explain to me the instruction

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

[MayilVahanan]

Hi

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.,