11 Replies Latest reply: Mar 19, 2012 10:05 AM by Dayna Litherland

# Sum of Row Values in Pivot Table

All,

I'm hoping someone can point me in the right direction, I want to do a calculation on a pivot table to work out the percentage for each item, so I need to get the value for the total of the row... i.e.

Product A Total: 4000

When expanded, you see the subtotal for each sub-product:

P1 2000

P2 1000

P3 1000

So my calculation would be:

P1 4000/2000

P2 4000/1000

P3 4000/1000

I've tried TOTAL, but I don't want the total for all of the products, just the product related to the sub-product.. Hope this makes sense!

Kind Regards,

Dayna

• ###### Sum of Row Values in Pivot Table

As per my understanding you want the percentage of a product from total.

Regards

Neetu SIngh

• ###### Re: Sum of Row Values in Pivot Table

Hi,

Use group in the TOTAL

Sum(Sales)/Sum(TOTAL <SubProducts> Sales)

• ###### Re: Sum of Row Values in Pivot Table

Neetu, by using TOTAL - I get the total for all products, but what I need is the sub total for each product for the sub-products, so unfortunately that doesn't work.

I tried using the group in the total, and I get the below - should the expression for Part 4054X be 3437?

Thanks,

Dayna

• ###### Re: Sum of Row Values in Pivot Table

qlikuser14,

Thank you for your post, but I use set analysis in my calculation - so I don't think I can use AGGR - and I need it in one column.. Do you have any other suggestions?

Many thanks,

Dayna

• ###### Sum of Row Values in Pivot Table

You can do it in one column, I just gave you all the formulas in different columns so you can understand it. What do you mean you use set analysis? It would be the same... If you give me an example I tell you how to do it.

• ###### Re: Sum of Row Values in Pivot Table

Here you go

• ###### Re: Sum of Row Values in Pivot Table

Hi Dayna,

you can use this

• ###### Re: Sum of Row Values in Pivot Table

Hi Dayna,

Aggr function will work with Set Analysis, as i am also using set analysis & aggr function in a single expression as mention below:

sum(aggr(sum({<Store={"=rank(sum(Sales))<3"}>} Sales),Store,Customer)).

Regards

Neetu Singh

• ###### Sum of Row Values in Pivot Table

O great, I'll give it a go! For some reason, I thought you couldn't use Set Analysis and Aggr together.. (If you can, then great!)

For the record, my calculation is:

sum

Kind Regards,

Dayna

• ###### Sum of Row Values in Pivot Table

Can you try this, but plz check your set analysis expression as in addmonths 'mmm yyyy' it shows error:

/

• ###### Re: Sum of Row Values in Pivot Table

Still no joy I'm afraid, it just comes up blank.. I'll try and get an example application sorted. For the moment, I wouldn't worry about the full expression - just the total [ sum(aggr(sum({'\$(=date(addmonths(today(),-1),'MMM YYYY'))'},CV_tr_type = {'ISS-WO'}>} CV_tr_qty_loc),SubProduct)) ] would be wonderful.

Many thanks for your continued help!

Kind Regards,

Dayna