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

# Creating group by or subquery in Tableau

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

• ###### 1. Re: Creating group by or subquery in Tableau

We can't view the workbook as it is a live connection to your SQL Server. Yes, this can be computed, either via table calculations, or if not, then certainly by LoD expressions.

• ###### 2. Re: Creating group by or subquery in Tableau

Ok, looks like I can't attach the workbook because its from a production environment. I have used LOD but it has not worked.

I have table calculations in the book but they dont work. So not sure what to do at this point

• ###### 3. Re: Creating group by or subquery in Tableau

Perhaps a screenshot or an excel example with some dummy data. If you include the numbers you are trying to achieve and how those are calculated, that will speed things up.

• ###### 4. Re: Creating group by or subquery in Tableau

Thanks for the input. I have included a screen shot as well as how the metrics are calculated and what they should actually equal. Let me know if there is anything else that would be helpful

• ###### 5. Re: Creating group by or subquery in Tableau

Try computing them in this way:

Net Impact:

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

Non influenced:

AVG({INCLUDE [Skillgroup] :avg(if [influenced ind]=0 then [att] end)})

Overall Att:

avg([att])

Can you show us what the output looks like if you do this? It's definitely possible, just difficult to do without a sample workbook.

1 of 1 people found this helpful
• ###### 6. Re: Creating group by or subquery in Tableau

When I do it like that, the results are 14.1%.

I then tried to use include on the overall att too, this brough the results to 6.4% which is closer.

I am starting to wonder if its because the SQL has a weighting by the number of calls

• ###### 7. Re: Creating group by or subquery in Tableau

Using include on the two other metrics instead of doing it for net impact is the correct way. Thank you for that. I realized in order to get the correct answer, I need to find a way to multiply the non influenced and the overall att by the number of calls as is done in the SQL.

This will provide the correct answer. I just need to figure out the best way to do this.