Qlik Community

QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

darrihilmarsson
New Contributor II

How to get right total in Pivot table?

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 RefWeightFlag
VAGS003VAGS0012,3N
VAGS003VAGS0025,8N
VAGS003VAGS0038,1Y
AARS002AARS0026N
Tags (1)
1 Solution

Accepted Solutions
Highlighted
preminqlik
Valued Contributor II

Re: How to get right total in Pivot table?

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]))

2 Replies

Re: How to get right total in Pivot table?

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,

Regards,
Prashant Sangle
Highlighted
preminqlik
Valued Contributor II

Re: How to get right total in Pivot table?

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]))

Community Browser