topic Re: get the sum of rows from pivot table (and not total of expression) in QlikView

get the sum of rows from pivot table (and not total of expression)

cafcptg2011 — Wed, 21 Nov 2012 16:22:53 GMT

Hi,

I need to get sum of rows from a pivot table and put it in a text object. I have joined qvw doc to show you my problem.

Can anyone help me please?

Sorry my newbie question!

Thanks in advance,

Re: get the sum of rows from pivot table (and not total of expression)

Anonymous — Wed, 21 Nov 2012 16:34:47 GMT

The expression doesn't look logically correct. Try this in both table and text box

=sum( SalesQty* Cost)

Regards,

Michael

Re: get the sum of rows from pivot table (and not total of expression)

cafcptg2011 — Wed, 21 Nov 2012 16:37:58 GMT

thank you for your reply. Your expression gives 590!! and not 880 as should be!

Re: get the sum of rows from pivot table (and not total of expression)

Anonymous — Wed, 21 Nov 2012 16:51:15 GMT

I know - and looking t the data I belive that 590 is the correct result, not the 880.

Re: get the sum of rows from pivot table (and not total of expression)

cafcptg2011 — Wed, 21 Nov 2012 17:17:23 GMT

sorry, but I need the multiplication between the column sum(SalesQty) and Sum(Cost) and not Sum(SalesQty * Cost). It gives completly different results. the first table is what i want.

I need to get the result in a text object, can you help me.?

thanks

Re: get the sum of rows from pivot table (and not total of expression)

Anonymous — Wed, 21 Nov 2012 17:27:14 GMT

Maybe, if you convince me that your logic is correct... So far I cannot get it.

Look at the store #1. In your data, you have 4 sales with cost =2, and five sale with cost =1. The store sales is

4*2 + 5*1 = 13

What is wrong with it?

Your expression for store #1 (4+5)*(2+1) = 27

What is the meaning of it?

Regards,

Michael

Re: get the sum of rows from pivot table (and not total of expression)

cafcptg2011 — Wed, 21 Nov 2012 17:46:42 GMT

i need to first sum sales and cost and then make a multiplication between them. But, I dont' understand why you consider that is not logical.

As is possible the pivot table give the 880 as result, it also should be possible make expression (i think) to give that result.

Re: get the sum of rows from pivot table (and not total of expression)

Anonymous — Wed, 21 Nov 2012 17:49:25 GMT

It is possible, and it is simple,

=sum(aggr(sum( SalesQty) * sum( Cost), store))

but multiplying sum by sum doesn't make sense, sorry. Isn't it obviuos that the store 1 sales is 13 not 27?

Re: get the sum of rows from pivot table (and not total of expression)

cafcptg2011 — Wed, 21 Nov 2012 18:05:00 GMT

thank very much. i understand that it could make less sense view from outside. but for me make me total sense. And the expression that you gave me now works perfectly. So, thanks again.

regards,

cafc