7 Replies Latest reply on Aug 23, 2013 12:20 PM by Eric Decker

    Aggregation / Quantity Roll Up Calculation Issue

    Eric Decker

      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

      Raw Data.JPG

        • 1. Re: Aggregation / Quantity Roll Up Calculation Issue
          Robin Kennedy

          Hi Eric,

           

          One way to achieve this would be to create a calculated field which takes into account the number of times a NCSN appears in the list ... for example a formula such as

           

          sum([NC Qty]) / countd([NCSN])

           

          Would effectively de-duplicate the summation of each NC Qty

           

          To use the count distinct formula, you must be using an extract, because Excel doesn't support it.

           

          More on creating calculated fields here:How to Create a Calculated Field

          More on creating an extract here: Creating an Extract

          1 of 1 people found this helpful
          • 2. Re: Re: Aggregation / Quantity Roll Up Calculation Issue
            Eric Decker

            Robin,

             

            Thanks so much for the response! I made the data extract and am taking advantage of the countd in the formula, however I'm still not getting the appropriate "True NC Qty". After applying the formula and generating the crosstab for my results, I'm seeing the total sum for Hours Spent as 23.3 (correct), NC Qty (as a general sum) of 289 (correct) but the new calculated measure "True NC Qty" is showing only 17, when it should be showing a qty of 241. Having said that, there are 17 distinct NC numbers so I feel like this is very close to solving my problem. Is there a way to do a sum of the distinct numbers that could be included in the formula to get the total sum to equal 241?

             

            Thanks again so much, this has been extremely helpful!

            Also, I've attached my work book and excel file for you to see what I'm looking at.

            • 3. Re: Aggregation / Quantity Roll Up Calculation Issue
              Matt Lutton

              Hi Eric:

               

              The best way to upload to the forum is to provide a packaged workbook: Packaged Workbooks

               

              This makes it far easier for us to look at your work and attempt to help.    

               

              Cheers.

              • 4. Re: Aggregation / Quantity Roll Up Calculation Issue
                Robin Kennedy

                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...

                 

                Capture.PNG.png

                 

                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

                • 5. Re: Aggregation / Quantity Roll Up Calculation Issue
                  Robin Kennedy

                  Just one more thing... the third option here is to change the way the data is delivered to you and put into Tableau...

                   

                  There are 2 levels of granularity here, which is why we're getting these issues. One level is a hours spent per each 'job' on the item, and the other is the item and quantity itself. They've been joined together and you get a "fan trap" - duplicate fields for NC Qty.

                   

                  If these pieces of data are stored in separate tables in a database, you could bring them into Tableau separately, and then blend the data together, but that's a story for a different day

                  • 6. Re: Re: Aggregation / Quantity Roll Up Calculation Issue
                    Eric Decker

                    Thanks Matthew, I'll be sure to do that in the future! 

                    • 7. Re: Re: Aggregation / Quantity Roll Up Calculation Issue
                      Eric Decker

                      You're the man Robin! I went with your option two, since that's a pretty simple way to do it. But I still like the CountD function, and I have a feeling I'll be able to use that in the future --and now I know that I need to extract to even get to that functionality. Also, we're currently joining about 3 different data sets together as it is (we're trying to simplify this to only needing to join two databases together in the future), and while doing joins definitely can make things more interesting, it's very powerful Tableau handles it very well.

                       

                      Thanks so much for your help, using this will get me where I need to do be! Such an easy solution for a month old problem .

                       

                      -Eric