How to get right total in Pivot table?

[Anonymous]

Hi QlikView community

I got this database in the table below me.

I have a master number for every job ref in the data

But my data is setup up in that way that when you have many job ref in the same master nr the job ref VAGS003 adds weight of the VAGS001 + VAGS002 together

So the weight for job ref VAGS003 is 2,3 + 5,8 = 8,1.

But actually it is 0 not 8,1 like my data is saying.

Sometimes I have the just one Job Ref in one Master nr. e.g. AARS002 and that is no problem

I have signal for when the master nr equals the Job Ref (Flag = 'Y')

So I used the formula

=if(Flag = 'N', SUM(Weight),

sum (Aggr ([Master nr.] = [Job Ref],'0',(Weight), [Job Ref])))

and it worked okay but not for the total weight.

I don't want to use sum of rows because I want to have Pivot table.

What can I do?

Master nr.	Job Ref	Weight	Flag
VAGS003	VAGS001	2,3	N
VAGS003	VAGS002	5,8	N
VAGS003	VAGS003	8,1	Y
AARS002	AARS002	6	N

[PrashantSangle]

Hi,

Try this in your expression.

=if(Flag = 'N', SUM(Weight),

if([Master nr.] = [Job Ref],Above(Sum(Weight))+Above(sum(Weight),2),'0'))

Please Find attached example It is as per your requirements.

Regards,

[preminqlik · Accepted solution]

hi there, try with beow exp

=sum(aggr(

if(Flag = 'N', SUM(Weight),

sum (Aggr ([Master nr.] = [Job Ref],'0',(Weight), [Job Ref]))),[Master nr.],[Job Ref]))