Hello,
I'm a fairly new Tableau user with little to no programming expertise and I'm trying to get past an aggregation problem that I've encountered since I first started using the software. I'm trying to get a calculated measure that I can apply to a dashboard.
Below is an altered example of part of a data source I'm using to build my dashboards.
In a nutshell, when rework needs to be done in our manufacturing process, we collect certain data elements that are tied back to a nonconformance record called the nonconformance serialized number (NCSN seen in column A). The measures data that we report on, are the amount of rework hours (Hours Spent seen in column B), and the number of nonconforming parts associated with the rework (NC Qty seen in column C).
For some rework operations, there may only be one rework step with Hours Spent and NC Qty tied back to a NCSN, as seen in Row 1. Getting the Hours Spent and NC Qty for this as a sum is easy. However, when multiple rework steps are needed, we capture the Hours Spent and NC Qty at each step along the way, referencing the same NCSN number. For example, if looking to Rows 24 thru 27, we see that there is a total of 13.24 Hours Spent and (if doing a SUM) 4 NC Qty.
This is where my problem begins: I’m trying to find a way to roll up the NC Qty respective to an NCSN without overstating the line item NC Qty. I want to report on an NC Qty of 1, and not as 4 as there was only 1 nonconforming part that was run through 4 different rework sequences. I’ve done searches on possible remedies for this but have yet to find anything. I have to imagine that Tableau has a way of doing this in a table calculation of some kind.
Does anyone know how to create a measure for NC Qty that’s tied to the NCSN without duplicating the NC Qty over several rows? As another example, using the below data, if I wanted to use the dimension of Process and only filtered for “Deburr”, the end goal would be to see the corresponding Hours Spent to be 2.18 and the NC Qty to be 14.
Any and all help and suggestions would be greatly appreciated. Once I understand the concept behind this, it will also allow me to do similar calculations against other fields in other data sets.
I've also included the Excel file for ease of use.
Thanks in advance for your assistance,
Eric
Hi Eric,
Ahhh, didn't appreciate this one fully... Looks like we have a couple of options to do this.
One is to use table calculations -- we need to work out that True NC Qty at a NCSN level, and then sum them up, rather than doing it at a higher level (e.g. by Process, or by <nothing>) so we have to keep NCSN in the view somewhere - on the marks card works for this one. Then the table calculations do the summing up after the initial aggregation (i.e. sum(NC Qty) / count(NCSN) ) has been completed at the NCSN level... I made a mistake before, it's not countD after all, just COUNT -- this calculation is therefore just the same as average!
Phew.. that's probably a bit advanced for a beginner user so I have a second, simpler option for you.
We can do some aggregation whilst creating the extract itself. We want the sum of all the hours, but only the 'average' of all the times the NQ Qty appears for each NCSN. When we do an extract, there is an option in the middle of the extract dialogue box saying Aggregate up for visible dimensions. This will do some aggregation in the extract itself. We want to chose this option, but only after we have set the default aggregation for the field NC Qty to average -- right click it in the data window on the left and select default properties > aggregation > average. Then we can do the extract using the option...
I've attached a packaged workbook for you with these two options as refrence. As someone else pointed out already, when you upload a workbook, make sure you save it as a 'Packaged' workbook as this ensures that the data is included with the viz
