5 Replies Latest reply on Jan 9, 2020 9:29 AM by Eric Smith

    Calculated field help

    Eric Smith

      Hello I am trying to create a calculated field - below is the calculation - what am I missing? Thanks

       

      IF [Title] = KAE then SUM([Rule 21])/3000

      ELSEIF [Title] = KAE 85% then SUM([Rule 21])/3000

      ELSEIF [Title] = SKAE 85% then SUM([Rule 21])/3000

      ELSEIF [Title] = KAE 90% then SUM([Rule 21])/3000

      ELSEIF [Title] = SME-75% then SUM([Rule 21])/8000

      ELSEIF [Title] = SME-75%/85% then SUM([Rule 21])/8000

      ELSEIF [Title] = SME-75%/90% then SUM([Rule 21])/8000

      END

        • 1. Re: Calculated field help
          swaroop.gantela

          Eric,

           

          Because your are summing [Rule21],

          I suspect it probably needs the [Title] to be aggregated.

          Maybe ATTR() will serve the purpose:

           

          IF ATTR([Title]) = "KAE" then SUM([Rule 21])/3000

          ELSEIF ATTR([Title]) = "KAE 85%" then SUM([Rule 21])/3000

          ...

          END

          • 2. Re: Calculated field help
            Eric Smith

            I didn't get an error this time, but the end result calculations did not come out right?

             

            IF ATTR([Title])= "KAE" then SUM([Rule 21])/3000

            ELSEIF ATTR([Title])= "KAE 85%" then SUM([Rule 21])/3000

            ELSEIF ATTR([Title]) = "SKAE 85%" then SUM([Rule 21])/3000

            ELSEIF ATTR([Title]) = "KAE 90%" then SUM([Rule 21])/3000

            ELSEIF ATTR([Title]) = "SME-75%" then SUM([Rule 21])/8000

            ELSEIF ATTR([Title]) = "SME-75%/85%" then SUM([Rule 21])/8000

            ELSEIF ATTR([Title]) = "SME-75%/90%" then SUM([Rule 21])/8000

            END

            • 3. Re: Calculated field help
              swaroop.gantela

              Eric,

               

              There may be other factors in your workbook that are affecting the end result.


              Would be grateful if you could post a workbook with fake data that looks similar to your true setup, and if you could also post an excel spreadsheet of what you

              expect the numbers to come out to be.

               

              Often times, the issue is the granularity at which the calculation is taking place.

              The SUM(Rule21) may be happening at a level of a Customer (for example)

              but your workbook is looking at a Store level.

              • 4. Re: Calculated field help
                Jim Dehner

                Hi Eric

                 

                you say you didn't get the correct result - what did you get

                It would help to see your TWBX workbook with the data -

                 

                thanks

                Jim

                • 5. Re: Calculated field help
                  Eric Smith

                  You know actually I rechecked the calculations end result and they are right - thanks again.