7 Replies Latest reply on Feb 23, 2016 8:39 AM by Eric Volker

    Creating group by or subquery in Tableau

    Eric Volker

      I am trying to do a calculation where I group by a dimension that I have (skillgroup). However, when I try to do so, I am not getting the correct answer. I know that the calculation is correct without the group by. But when I try to use include or other ways to look at it by the skillgroup, the answer is not correct. Basically, I am trying to find a way that I weight the calculation by the skillgroups. I am not sure if I need to have the data set up in a different way or if there is a way to do this in a calculation.

       

       

       

      I have included some screenshots with the metric. It is the correct calculation without the skillgroup weighted. With the weighted skillgroup, the metric should equal to 5.8%

      Here is how the metrics are calculated from the screenshot:

       

      Net Impact:

      ([non influenced])-[overall att])/[non influenced att]

       

      Non influenced:

      avg(if [influenced ind]=0 then [att] end)

       

      Overall Att:

      avg([att])

       

      Total Calls:

      count([call id])

       

       

      For reference, I provided how the correct answer is obtained in SQL using a subquery

       

      select ((sum(n.niatt*n.totalcalls)/sum(n.totalcalls))-(sum(n.tatt*n.totalcalls)/sum(n.totalcalls)))/

           (sum(n.niatt*n.totalcalls)/sum(n.totalcalls)) as ni

      from (

      select

      count(callid) as totalcalls

      ,avg(att) as tatt

      ,avg(case when influenced=0 then att else null end) as niatt

      from data

      group by skillgroup

      )n