6 Replies Latest reply on Jun 13, 2017 6:45 AM by arvindgarg

    Calculations between 2 worksheets

    Saket Metkari

      Hi Team,

       

      I want to perform calculations from 2 different worksheets, can you please let me know the formula.

      I need sheet 2 -sheet 3 - sheet 4 = Result (In percentages)

       

      I am trying this

       

      "(" +

      str({fixed: SUM([Hours])}) +

      " - " +

      str({fixed: SUM([Hours])}) +

      " = "

      + str({fixed: STR([Hours])})

       

      But it's not working, can you please help me.

       

      thanks !

      SAKET

        • 1. Re: Calculations between 2 worksheets
          Wilson Po

          Hi Saket,

          No need for the LOD fixed calculations, from what it looks like you simply need to use boolean statement to replicate the [Work Type] filter you have on each sheet:

               SUM(IF( [Work Type] = "BILLABLE" THEN [Hours] END) - SUM(IF( [Work Type] = "PRODUCT WARRANTY" THEN [Hours] END) - SUM(IF( [Work Type] = "SERVICE WARRANTY" THEN [Hours] END)

          The results should be the total number of hour remaining. If we call this field [Hours Remaining], you can compute the percentage by comparing to the overall "BILLABLE" again:

               [Hours Remaining] /  SUM(IF( [Work Type] = "BILLABLE" THEN [Hours] END)

          This reading might help with any future logical calculation work you may need to do: Logical Functions

          1 of 1 people found this helpful
          • 2. Re: Calculations between 2 worksheets
            arvindgarg

            I will also add  else 0    in all the if else statements . Because , if it evaluates to NULL .  Final  calculation will return NULL.

             

            Something Like below

             

            IF( [Work Type] = "BILLABLE" THEN [Hours]

            ELSE  0

            END

            • 3. Re: Calculations between 2 worksheets
              Wilson Po

              Hi Ari,

              That's not necessarily true. Most DBs understand how to aggregate across NULL values in a field without issue;  this includes Tableau's data engine when working with flat files or extracts.  The only situation where NULLs will result in NULL returns is if it is part of a mathematical operation, such as if one of the above sums are actually NULL:  i.e. Value -NULL-Value= NULL.  This might be a valid data problem to call out, so I would not always suggest assuming zeros to complete a calculation is the appropriate approach. 

               

              I will also point out that Adding "ELSE 0" will also tend to affect non-additive aggregation results;  if we did AVG() on the fields, it may assuming that non "BILLABLE" rows are valid rows to consider in the Average calculation down and drag our mean results down. Something to consider.    

              1 of 1 people found this helpful
              • 4. Re: Calculations between 2 worksheets
                arvindgarg

                Hi Wilson

                 

                I do not disagree with you . But i always use else 0 in these kind of situations . So i replied here.

                • 5. Re: Calculations between 2 worksheets
                  Shawn Wallwork

                  Huh? Ari why would you 'always use' something that could/will skew your results? If you agree with Wilson, then why continue/promote a 'bad' habit?

                   

                  --Shawn

                  • 6. Re: Calculations between 2 worksheets
                    arvindgarg

                    Sir

                     

                    I am not promoting anything here. Whenever i used that it may not have skewed the result.

                     

                    I believe people reply in this forum based on their experience level. Some may have more experience and some may have less.

                     

                    I agree with Wilson's explanation, which he did after my reply to this thread.

                     

                    I hope , it clarify the things.

                     

                    Ari