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.