1 Reply Latest reply on Oct 12, 2018 11:19 AM by Yuriy Fal

    Using subtotals in Line chart

    Bryan Talaban

      Good day,

       

      I have a record of Time-in/out of employees that using Google sheets. This is the format of the google sheet:

       

       

      After importing this data into tableau, I made a calculated field called Time in and Time out:

       

      Time in - IF [Status]='IN' THEN DATETIME([Value]) END

      Time out - IF [Status]='OUT' THEN DATETIME([Value]) END

       

      Using this formula, I made Hours Rendered Calculated field:

       

      Hours rendered = INT(ZN(DATEDIFF('hour',Max([Time in]),Max([Time out]))))

       

      I used Max (I can use min as well) on both because if I don't, the answer will be null. The data looks like this:

       

       

      So basically i'm subtracting the non-null values 5:19PM - 6:49AM to get the hours rendered.

       

      So the problem is I can make the visualization if its just text (with subtotals):

       

       

      The Day of Start Week is referring to Mondays, and the formula I used is: DATETRUNC('week',[Date])+1


      What I need is the value of subtotal (24,38,29,etc.) In my line chart. However, I can't use this values because I can't get the subtotal if I use a line chart. I tried using window_sum,running_total,sum, but the formula doesnt get what I want.

       

      What I need is the A line chart consisting of the weeks and the total for each week. Something like this but combined values for each Monday (Week start)

       

      Sum of hours rendered from july 2,july 9, 16, 23, and so on.

       

       

      Thanks and sorry for the long post!

        • 1. Re: Using subtotals in Line chart
          Yuriy Fal

          Hi Bryan,

           

          You may want to calculate the [Hours Rendered per Day] like this:

          {INCLUDE [Date] : DATEDIFF('hour', MIN( [Time In] ), MAX( [Time Out] ) ) }

          then use this pill (as a SUM() aggregate) on a view on Rows,

          your [Day of Week Start] pill on Columns, a Line as a Chart Type.

           

          Chances are you'd get the line chart.

           

          Yours,

          Yuri