5 Replies Latest reply on May 22, 2013 12:17 AM by Jim Wahl

    Aggregation and Disaggregation with Table Calculations - The Conundrum.

    Chandra Shekhar Jain

      The objective is to find out the Scrap Value per Work Order. Seems innocent.

       

      I have wrestled with this problem for about 6 Hrs now and am nowhere near the solution.

      Please see attached excel sheet and twbx file for the base data and business logic.

       

      Any help will be greatly appreciated.

       

      Chandra

        • 1. Re: Aggregation and Disaggregation with Table Calculations - The Conundrum.
          Jim Wahl

          Hi Chandra,

           

          I don't fully understand your formula for scrap value, but I was able to get the sum of scrap value for each work order by creating a new table calculation that is a WINDOW_SUM([Scrap Value]).

           

          Two things to keep in mind. First, you'll need Oper Seq in the view, since it's a level of detail to calculate the sum of product of component issued qty * cost / unit. But it does't have to be visible; moving it to the level of detail button is fine.

           

          Second, the "compute using" for all of the table calcs is critical once you start moving the pills around. Rather than use Pane (down), I explicitly specified the addressing. In this case you want to address by Part Nbr and Oper Seq and then partition by WO Nbr. You need to do this from the advanced dialog box; from the pill, right-click > Edit Table Calculation. For each calculated field in the pull down menu, select Compute Using advanced and move Part Nbr and Oper Seq to the right-hand side.

           

          2013-05-21 19-58-58.png

           

          Let me know if this isn't clear.

           

           

          Jim

          • 2. Re: Aggregation and Disaggregation with Table Calculations - The Conundrum.
            Chandra Shekhar Jain

            Thanks Jim

             

            I had used the Advanced option in the Compute Using in my sheets.  However always go stuck in removing the multiple values shown when I moved Part Nbr to the detail.

             

            I did not know that you could show a single number using 

            if first==0 window_sum() end

             

            Thanks again

             

            Chandra

            • 3. Re: Aggregation and Disaggregation with Table Calculations - The Conundrum.
              Jim Wahl

              No problem. You'll probably end up using the if FIRST()==0 trick a lot.

               

              This used to be obvious in Tableau 7, since the values / marks were on top of each other.

               

              Tableau 8 introduced "stacked marks" and even with the IF FIRST() trick, you'll have null values for the remaining partitions. To eliminate the white space resulting from the nulls, select Analysis > Stack Marks > Off ---- this gets you back to Tableau 7 behavior.

               

              Jim

              • 4. Re: Aggregation and Disaggregation with Table Calculations - The Conundrum.
                Chandra Shekhar Jain

                There you go. This time without me even asking the question.

                I was trying to right align the number but it was not happening. Did not realize that those were the null values showing up.

                 

                Thanks again.

                 

                Guess what is the issue I am facing now ?

                 

                Weighted Average to show as a reference line instead of a Simple Average. (In a different Work sheet. making a graph for scrap % by Work Center)

                 

                Chandra

                • 5. Re: Aggregation and Disaggregation with Table Calculations - The Conundrum.
                  Jim Wahl

                  Good luck with the reference lines.

                   

                  Reference lines can be deceptively tricky, especially when there are table calcs or aggregates in thew view.

                   

                  The common example is if you plot average sales by category and then add a reference line for Sales Avg, the reference line will be an average of the averages, not the overall sales average, which is what most people want.

                   

                  In this case, you can create a field that is TOTAL(AVG([Sales])) and then use this field as the reference line value. The TOTAL function removes a level of aggregation.

                   

                  It's just very important to double-check the values Tableau reports---with aggregations, table calcs, reference lines, it gets complicated.

                   

                  Jim