4 Replies Latest reply on Oct 1, 2012 1:22 AM by Ertan ERISIK

    New Calculated column in the sheet

    Ertan ERISIK

      Hi,

       

      I am quite new with Tableau and I need help for the following problem.

      I have created a sheet which looks like following table.

       

       

      Hour
      IN
      Out
      6127
      7123
      81414
      9136
      101312

       

       

      And I would like to add another column, which will be a calculated field from IN and Out columns such that:

      HourInOutCalculated Field
      612712
      712317
      8141428
      913627
      10131234




       

      Basically I want a new column in the sheet separate from that of the panes provided by the query and  in each row of this column, I would like to extract running total of OUT till the current row-1, from the running total of IN till the current row.

      Would this be possible with the current table calculations or do I have to model the database accordingly or write a custom SQL?

       

      Any idea will greatly be appreciated.

        • 1. Re: New Calculated column in the sheet
          Tracy Rodgers

          Hi Ertan,

           

          This can be done using a calculated field similar to the following:

           

          running_sum(sum(IN))-lookup(running_sum(sum(OUT)), -1)

           

          Depending on how your view is set up the way the calculation is being computed may need to be edited (right click on it and select Compute Using...)

           

          Hope this helps!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: New Calculated column in the sheet
            Ertan ERISIK

            Tracy Hi,

            Thanks a lot for the prompt response and please accept my apologies for not describing the problem  accurately. The two columns in the above table are  in fact from the same database field  separated according to type column in the same table. So I cannot refer them individually within the Tableau  sheet as in your example.

             

            Best Regards,

             

            Ertan

            • 3. Re: New Calculated column in the sheet
              Mark Holtz

              It sounds like you are describing a table structure like this:

              table structure.JPG

              So the number field is the only measure, and the Type field is a dimension that you use to create the cross-tab in your original post...

               

              If so, you can still use Tracy's formula, you just need to create new measures for each type.

              IN:

              IF [Type] = 'IN'

              THEN [CountField]

              ELSE 0

              END

               

              OUT:

              IF [Type] = 'OUT'

              THEN [CountField]

              ELSE 0

              END

               

              I've attached a workbook with this info.

              The only catch is the table calculation cannot run for the first row because there is "previous" row for which to get lookup information in the table. 

              1 of 1 people found this helpful
              • 4. Re: New Calculated column in the sheet
                Ertan ERISIK

                Thanks guys,

                 

                I have used the tips given here and changed a little bit my workbook and it seems everything is fine now.

                 

                Thanks a lot.