4 Replies Latest reply: Jun 20, 2012 2:52 PM by marn01 RSS

    Using contents of (a non selected) Field as another Field

      Hello all,

      QV9 User.

       

      I have a financial report where I have calculations to be used in a ratio, the ratio divisor being specific rows in the report, not one divisor per column. I have the report working successfully using a series of IF statements, but I want to replace that with a more efficient and speedy calculation, removing the IF conditions.

       

      [BUD01.Divisor] is a field that contains the names of several other fields.

       

      if( [BUD01.Divisor] ='NETSALES' ,    

      // sum Net Sales

      sum(TOTAL PeriodBalance * NETSALES)),

       

      if( [BUD01.Divisor] ='WHS' ,    

      // sum WH Sales

      sum(TOTAL PeriodBalance * WHS)),

       

      if( [BUD01.Divisor] ='EXP' ,    

      // sum WH Sales

      sum(TOTAL PeriodBalance * EXP)),

       

      AND SO ON for each BUD01.Divisor value in the report.

       

      After looking on the forum, I have tried a few solutions. Basically I want the fieldname in BUD01.Divisor to act as field in itself.

       

      I thought this might work

      sum(TOTAL PeriodBalance * $(=([BUD01.Divisor]))

       

      (this only works, say for NETSALES, if NETSALES is selected, but that is no good for any of the other divisor values).

       

      Has anyone any suggestions on this, or do I need to add logic to the script to handle this.

       

      Many thanks for any help.