4 Replies Latest reply on May 10, 2012 2:38 PM by jared.thatcher

    how to do a Total of a Calculated Field

      I have this if Statement as a calculated field

       

      if[All Search Unit Total] < 4 THEN 47

      elseif[All Search Unit Total] < 7 THEN 46

      elseif[All Search Unit Total] < 10 THEN 45

      elseif[All Search Unit Total] < 13 THEN 44

      elseif[All Search Unit Total] < 16 THEN 43

      elseif[All Search Unit Total] < 18 THEN 42

      elseif[All Search Unit Total] < 20 THEN 41

      elseif[All Search Unit Total] < 22 THEN 40

      elseif[All Search Unit Total] < 24 THEN 39

      elseif[All Search Unit Total] < 26 THEN 38

      elseif[All Search Unit Total] < 28 THEN 37

      elseif[All Search Unit Total] < 30 THEN 36

      else 35 end

       

       

      It works just fine for everything except the Total.  This logic applies to the total making the Total 35.  What I want is the actual total as an average but as long as the total is being calculated by the If Else statement I can't

       

      Any help would be greatly appreciated,

       

      here is a small snipit of what it returns

      Measure Values
      35
      38
      35
      37
      35
      47
      TOTAL
      35
        • 1. Re: how to do a Total of a Calculated Field
          Tracy Rodgers

          Hi Jared,

           

          It is important to note that grand totals will use whatever aggregation is on the measure (i.e. your if then calculation will be applied to the grand total as well). Would you be able to post your workbook (saved as twbx file)? Maybe I/or someone else can come up with a work around.

           

          -Tracy

          • 2. Re: how to do a Total of a Calculated Field

            I'll see if I can get the sensitive data out and post it.

            • 3. Re: how to do a Total of a Calculated Field

              No doesn't look like I'll be able to post this one.

               

              I can't be the only one who has wanted to use a Total or Average of a calculated Field.  I could even add it in my IF statement if needed.  Something like

               

              if[All Search Unit Total] < 4 THEN 47

              elseif[All Search Unit Total] < 7 THEN 46

              elseif[All Search Unit Total] < 30 THEN 36

               

              else ( CURRENT FIELD'S TOTAL) end

               

              Except trying this gives me a circular reference Error.

              • 4. Re: how to do a Total of a Calculated Field

                Ok I found a workaround, Ghetto, But still a work around.

                 

                So I have my first calculated field called [Adj DAP Capacity] which does the ifstatment above.

                 

                I created a Second Calculated field and assigned it to = WINDOW_AVG(ZN([Adj DAP Capacity])).  This takes my the average of the calculated field in the new row.  Total is still broken but it provides the average I needed

                 

                So it looks like \/ the 38.8 is the number I wanted in the total but this works for now.

                 

                Adj DAP Capacity          Average

                36                                  38.8

                42                                  38.8

                45                                  38.8

                43                                  38.8

                47                                  38.8

                TOTAL                           Total

                35                                  35