Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Dayna
Creator II
Creator II

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

11 Replies
neetu_singh
Partner - Creator III
Partner - Creator III

Can you elaborate your question??

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

So, your calculation should be:

sum(amount received) / sum(total amount received).

Regards

Neetu SIngh

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Use group in the TOTAL

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

jvitantonio
Luminary Alumni
Luminary Alumni

Here you go

Dayna
Creator II
Creator II
Author

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

Dayna
Creator II
Creator II
Author

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

jvitantonio
Luminary Alumni
Luminary Alumni

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.

neetu_singh
Partner - Creator III
Partner - Creator III

Hi Dayna,

you can use this

(sum(amount received) / aggr((sum(amount received)),sub product) )

neetu_singh
Partner - Creator III
Partner - Creator III

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

Dayna
Creator II
Creator II
Author

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

={'$(=date(addmonths(today(),-1),'MMM YYYY'))'},CV_tr_type = {'ISS-WO'}>} CV_tr_qty_loc)

Kind Regards,

Dayna