10 Replies Latest reply: Nov 7, 2011 10:46 PM by Kiran Rokkam RSS

    Add formulas to existing fields in script

      Hi

       

      I have a field called Accounts with a number of account descriptions like Gross Profit and Invoicing.  I have another field called Amount which is the amount for each account.  I would like to create a couple of ratio formulas one of which is Gross Profit / Invoicing and I thought this would be best to do in the script.

       

      Ultimately I would like for these formulas to be in the same field as Accounts so that they all get displayed as a dimension.  I think that this will probably involve an inline load but I am having trouble with the expressions.

       

      Could someone please explain the best way to go about this.

       

      Thanks very much

        • Re: Add formulas to existing fields in script

          Hi Fiona,

           

          Lets say you have a table in script called Accounts with fields Customer,Product,AccountDescription,Amount.

           

          Adding the below script would create the ration you need in the table.

           

          Temp:
          Load Customer,Product, Amount as AmountGP Resident Accounts
          where AccountDescription = 'Gross Profit';
          Join(Temp)
          Load Customer,Product, Amount as AmountInv Resident Accounts
          where AccountDescription = 'Invoice';
          
          
          Concatenate(Accounts)
          Load Customer,Product,'GP/Invoice' as AccountDescription,AmountGP/AmountInv as Amount Resident Temp;
          
          Drop Table Temp;
          

           

          Hope this helps.

          Kiran.

          • Re: Add formulas to existing fields in script

            Hi Kiran

             

            Thanks very much that's part of the way there.  I've got GP% label in the Accounts field now but it is not calculating correctly.  As the expression I am using for the rest of the dimension is sum(Amounts) I think it is working out the GP% for every line and then summing them together.

             

            I've attached the file as I'm sure this will be easier to work out if you can see what I'm trying to do.

             

            Would this be more suited to a variable?  I haven't worked with them very much.

             

            Fiona,