14 Replies Latest reply: Oct 17, 2016 5:14 PM by Sunny Talwar RSS

    Multiplying multiple variables against a field

    kosta christopher

      Hello,

       

      I have two fields that I am multiplying against each other.  Each field is in two different tables but are synced through a unique identifier field.   When I multiply them I have the following basic formula.

       

      sum(KM*(Multi/100))

       

      My problem is that for each row of the KM field I want to multiply a different variable against it.   I used dynamic update to do this originally but cannot use it on a server.  I want to give the ability to basically change the KM field.

       

      My first attempt was the following with each variable.

       

      sum(Multi*(KM/100))
      *(
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Alberta486',300,
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016British_Columbia486',122,
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Saskatchewan486',80,
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Quebec486',3,
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Northwest_Territories486',0,
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Ontario486',3,
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Manitoba486',40,
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016New_Brunswick486',0,
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Nova_Scotia486',0,
      if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Yukon486',0,
      10))))))))))
      )

       

      Note: 300 in the first if statement would actually be a variable and each other number below would be a variable as well that the user could change.


      The problem here is that unless I selected one of the strings in that unique identifier field it would just multiply everything by 10.  Which I don't want.  It would work, but I had to select as an example 'ConstructionGas2016Alberta486' and it would only work for that one.


      I then though why not just create a specific set analysis statement for each row instead of the if statement which I tried the following.


      300*sum({<PhaseTypeYearProvinceNAICS={'ConstructionGas2016Alberta486'}>}KM*(Multi/100))


      This worked a bit better but would still give me data for provinces that were not associated with Alberta as an example. 


      There are 600 rows in the KM field with a variable already created to adjust it.  Originally the variables were changed as a number you wanted to add or subtract from the "baseline" value in the KM field and then you a created a dynamic update script that would add/subtract the table based on updated variables.


      Any ideas on how to replace the dyanmic update method would be extremely helpful!!

       

      Thanks

        • Re: Multiplying multiple variables against a field
          Sunny Talwar

          May be try bringing the if statement within the Sum and see what happens:

           

          Sum(Multi*(KM/100)
          *(
          if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Alberta486',300,
          if(PhaseTypeYearProvinceNAICS='ConstructionGas2016British_Columbia486',122,
          if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Saskatchewan486',80,
          if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Quebec486',3,
          if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Northwest_Territories486',0,
          if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Ontario486',3,
          if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Manitoba486',40,
          if(PhaseTypeYearProvinceNAICS='ConstructionGas2016New_Brunswick486',0,
          if(PhaseTypeYearProvinceNAICS='ConstructionGas2016Nova_Scotia486',0,
          if(PhaseTypeYearProvinceNAICS
          ='ConstructionGas2016Yukon486',0,
          10))))))))))
          ))

          • Re: Multiplying multiple variables against a field
            Marcus Sommer

            I think Sunny is right - here a small adjustment to his suggestion:

             

            sum(Multi*(KM/100)
            *(pick(match(
            PhaseTypeYearProvinceNAICS, 'ConstructionGas2016Alberta486', 'ConstructionGas2016British_Columbia486', 'ConstructionGas2016Saskatchewan486','ConstructionGas2016Quebec486','ConstructionGas2016Northwest_Territories486','ConstructionGas2016Ontario486', 'ConstructionGas2016Manitoba486','ConstructionGas2016New_Brunswick486','ConstructionGas2016Nova_Scotia486','ConstructionGas2016Yukon486', '*'),
            $(v1), $(v2), $(v3), $(v4), $(v5), $(v6), $(v7), $(v8), $(v9), $(v10), $(v11))

             

            - Marcus

            • Re: Multiplying multiple variables against a field
              kosta christopher

              Yes, Sunny's suggestion is working now.  Thank you very much! 

               

              Marcus, I have never seen that formula before.  After looking at it, it makes sense to me though.

               

              Does this run faster than having all the nested if statements?

               

              One reason I like the nested if statements is that I can set a final else-if statement for all other rows in that column to go to zero without having to write in all 600 formulas for each row in like it seems I would have to do with the pick match method.