1 Reply Latest reply on Jul 12, 2018 5:14 AM by Mahfooj Khan

    Percentage of Sums based on different selection fields.

    oliver.engst

      Hi,

       

      I would like to do the following and I have no real idea how to achieve that.

       

      I have 1 data view that contains (beside others) those fields:

      (The data view is request based. So contains work time and qa time for single requests which are done by different people)

       

      RequestID (int)

      Timestamp (DateTime)

      Assignee (String)

      WorkedHours (Number)

      QAAssignee (String)

      QATime (Number)

      AvailableTime (Number)

       

      I would like to show now grouped by year, month and assignee a percentage of every persons usage. That means (time he worked+ the time he did QA) / AvailableTime.

       

      In SQL I would do that in a subquery like.

       

      SELECT Year(Timestamp)

           , Month(Timestamp)

           , Assignee

           , (

               ( (Select SUM(WorkedHours) FROM DataView vi WHERE Year(vi.Timestamp) = Year(v.Timestamp) AND Month(vi.Timestamp) = Month(v.Timestamp) and vi.Assignee = v.Assignee) +

                (Select SUM(QATime) FROM DataView vi WHERE Year(vi.Timestamp) = Year(v.Timestamp) AND Month(vi.Timestamp) = Month(v.Timestamp) and vi.QAAssignee = v.Assignee)) / v.AvailableTime) as Usage

      FROM DataView v

       

       

      As you can see, the second subselect is using the QAAssignee field to be the filter.

       

      How can I do that in Tableau?

       

      Every hint is appreciated.

        • 1. Re: Percentage of Sums based on different selection fields.
          Mahfooj Khan

          Hi Oliver,

           

          You can try this, not sure whether it'll work or not

           

          Create a calculated field like this

           

          Usage %:

           

          (SUM([WorkedHours]) + SUM(IIF([Assignee] = [QAAssignee] , QATime , 0)) / SUM([AvailableTime])

           

          Drag the date with datepart() Year, Month and Assignee then put Usage % on label or rows/columns shelf in canvas and check

          Else share some sample mock up data in packaged workbook so that any one of us can take a look and guide you to get the correct output.

           

          Mahfooj