topic Sum of Row Values in Pivot Table in QlikView

Sum of Row Values in Pivot Table

Dayna — Mon, 19 Mar 2012 10:49:47 GMT

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

neetu_singh — Mon, 19 Mar 2012 11:04:25 GMT

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

Re: Sum of Row Values in Pivot Table

CELAMBARASAN — Mon, 19 Mar 2012 11:08:36 GMT

Hi,

Use group in the TOTAL

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

Re: Sum of Row Values in Pivot Table

jvitantonio — Mon, 19 Mar 2012 11:11:57 GMT

Here you go

Re: Sum of Row Values in Pivot Table

Dayna — Mon, 19 Mar 2012 11:13:24 GMT

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

Dayna — Mon, 19 Mar 2012 11:17:24 GMT

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

jvitantonio — Mon, 19 Mar 2012 11:20:12 GMT

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

neetu_singh — Mon, 19 Mar 2012 11:20:33 GMT

Hi Dayna,

you can use this

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

Re: Sum of Row Values in Pivot Table

neetu_singh — Mon, 19 Mar 2012 11:27:43 GMT

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

Dayna — Mon, 19 Mar 2012 11:33:46 GMT

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

Sum of Row Values in Pivot Table

neetu_singh — Mon, 19 Mar 2012 11:44:59 GMT

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

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

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

Re: Sum of Row Values in Pivot Table

Dayna — Mon, 19 Mar 2012 14:05:44 GMT

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