2 Replies Latest reply: Jun 12, 2014 11:32 AM by Prem Kumar Thangallapally RSS

    How to get right total in Pivot table?

    Darri Hilmarsson

      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