1 Reply Latest reply: Jun 12, 2014 9:53 AM by Darri Hilmarsson RSS

    How to

    Darri Hilmarsson

      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 RefWeight
      VAGS003VAGS0012,3
      VAGS003VAGS0025,8
      VAGS003VAGS0038,1
        • Re: How to
          Jeremiah Kurpat

          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!