How to

[Anonymous]

Hi QlikView community

I got this database in the table below me.

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

But my data is setup up in that way that when you have the same master nr. and job ref they add the weight from the other job ref.

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. So I used the formula =if ( [Master nr.]=[Job Ref], '0', Sum(Weight))

But I noticed the problem that the total does not decline.

The total is 16,2 in the QlikView file but I want the total to be 8,1

Any ideas?

Master nr.	Job Ref	Weight
VAGS003	VAGS001	2,3
VAGS003	VAGS002	5,8
VAGS003	VAGS003	8,1

[jerem1234 · Accepted solution]

It is because in pivot table, the total evaluates the expression as well for the whole set. It does not just sum the rows. This is where dimensionality() can come in. You can use the expression:

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

But you can get away with just:

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

I listed the first one to show you how you can use dimensionality() if you couldn't have reduced it anymore.

Hope this helps!