12 Replies Latest reply on Apr 2, 2013 12:18 PM by Ashley Howard

    Aggregate and aggregate again?

    Brian Fung

      I am newbies to use Tableau. I have a question. If I have a master table with the trial balance values and top level department values, I need to split the trial balance from top level department values to next level department values based on different percentage. Afterthat, if the next level department values are not the bottom level, I need to split again based on department hierarchy. Is there any way for Tableau to do that? I am ok to retrieve the result for one level, however, the values will be incorrect if the department level is more than one. Right now, I am hanged in the middle of the way. Very appreciate if anyone have any suggestions on this.

        • 1. Re: Aggregate and aggregate again?
          Ashley Howard

          Have you tried using if statements? Something like...

           

               IF rank()=window_max(rank())

               ELSEIF [Value]<0.2 THEN [Next Level 1]

               ELSEIF [Value]<0.4 THEN [Next Level 2]

               END

           

          I'd be happy to help further with the calculation, but I find your explanation a bit confusing.  Could you provide example data or an example workbook?

          • 2. Re: Aggregate and aggregate again?
            Brian Fung

            Sorry for the confusions. Maybe I can show you the data sources and expected results below.

             

            SOURCE DATA (3 Excel spreadsheets)

            Dept        Amount

            700050      $5000

            700101      $10000

            700201      $12500

            701201      $5000

             

            DEPARTMENT HIERARCHY

            DEPT        SUB-DEPT ALLOCATION

            700101      700201      20%

            700101      700301      10%

            700101      700401      20%

            700101      700501      30%

            700101      700601      20%

             

            DEPT        SUB-DEPT ALLOCATION

            700201      701201      30%

            700201      701202      20%

            700201      701203      25%

            700201      701204      15%

            700201      701205      10%

             

            EXPECTED RESULT

            Dept        Amount

            700050      $5000

            701201      $600+$3750 = $4350

            701202      $400+$2500 = $2900

            701203      $500+$3125 = $3625

            701204      $300+$1875 = $2175

            701205      $200+$1250 = $1450

            700301      $1000

            700401      $2000

            700501      $3000

            700601      $2000

            • 3. Re: Aggregate and aggregate again?
              Ashley Howard

              Department Allocation.png

              To achieve the above you need to join the data and write some calculations, which I've outlined below.  Let me know if you encounter any problems. Good luck.

               

              1) Combine each excel file as one sheet in one excel file.

              2) Open a new Tableau File

              3) Select Connect to Data

              4) Select your Excel File

              5) Under Step 2 select Multiple Tables.  Each sheet should be its own table.

              6) Select table/sheet 1 with Dept and Amount, called Dept Allocation in attached.

              7) If any other tables/sheets are already selected remove.

              8) Add the second sheet by clicking on Add Table. On the Table tab, select the second table, called Dept 700101 in attached.

              9) Click on the join tab.  Tableau might do the join for you if they are named the same, but double check that from Dept Allocation dept is selected and from Dept 700101 select dept. Set the join to inner. 

              10) Repeat step 9 for each dept table.  Except this time you want to set the join to left.

              Side note:  If you're not familiar with joins this is my favorite resource: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

              11) Once you have all your tables connected, preview your results by clicking on the button.  Side note a great way to learn about joins is to play with them and see the results here.

              12)  Click Ok. And select your connection type to the data, I normally choose connect live.

              13)  Now we need to write some calculations to join the data further.

              14)  Create a new calculation named Sub Dept

                             ifnull(['Dept 700101$'_Sub-Dept], ['Dept 700201$'_Sub-Dept])

              15)  Create a new calculation named Allocation

                             ifnull(['Dept 700101$'_Allocation], ['Dept 700201$'_Allocation])

              16)  Create a new calculation named Sub Dept Amount

                             [Amount]*[Allocation]

              17)  You should be set. Create your vis/Check your work.  See sheets final/check work in the attached twbx file.

              • 4. Re: Aggregate and aggregate again?
                Ashley Howard

                Was just looking at your examples and saw the part about $600, $500, $400.  Can you give me a little more guidance... does the sub dept with the lowest allocation in the dept get $100, the 2nd lowest gets $100 more, etc... or does the highest start at $600 and the second highest $100 less?

                • 5. Re: Aggregate and aggregate again?
                  Ashley Howard

                  In case I can't respond before you need it I thought I would point you in the direction of the equation:

                                 (window_max(Index())-Index()+1)*100+sum([Sub Dept Amount])

                                 With the Compute using set to Sub Dept

                  • 6. Re: Aggregate and aggregate again?
                    Brian Fung

                    Hi Ashley,

                     

                    Thanks for your help on this. One question I have is that as you can see my example, 700101 has the sub-department of 700201 and 700201 has the other sub-sub-departments also. How can I handle for such situation?

                     

                    700101 > 700201 > 701201

                                              > 701202

                                              > 701203

                                              > 701204

                                              > 701205

                    700201 > 701201

                                > 701202

                                > 701203

                                > 701204

                                > 701205

                     

                    Thanks,

                    Brian

                    • 7. Re: Aggregate and aggregate again?
                      Brian Fung

                      Hi Ashley,

                       

                      I found that I cannot open the workbook you created because I am using Tableau version 7.Is it possible to save your version to a lower version in order to let me open?

                       

                      Sorry for the troubles.

                       

                      Regards,

                      Brian

                      • 8. Re: Aggregate and aggregate again?
                        Ashley Howard

                        Tableau doesn't allow you to save down.  I recreated it in version 7 and attached.

                        • 9. Re: Aggregate and aggregate again?
                          Brian Fung

                          Hi Ashley,

                           

                          Thanks for your help on this, however, I found that there is still having one issue not being resolved yet. That is for 700101 > 700201 > 701201, 701202, 701203, 701204, 701205. For my case, you can see that there are sub-departments for 700101 and one of them is 700201. Also there are sub-departments for 700201. That means, for the weighted amount of 700201 under the breakdown of 700101, it needs to continue breakdown to next level. Do you have any idea on how to achieve it?

                           

                          700101 (10000) > 700201 (2000) > 701201 (600), 701202 (400), 701203 (500), 701204 (300), 701205 (200)

                           

                          Thanks and regards,

                          Brian

                          • 10. Re: Aggregate and aggregate again?
                            Ashley Howard

                            The best approach would be to combine if and case statements in a new calculation and add it to the allocation amount. Replace Sub Allocation with the new calculation Weighted Allocation.

                             

                            Because Tableau aggregates up from the bottom most level (in this case Sub Dept) in calculating higher levels, your department numbers will be correct.  You can double check this by turning on sub totals and then removing Sub Dept from the Rows shelf.  The numbers should match.

                             

                            WEIGHTED ALLOCATION:

                             

                            Sum([Sub Allocation]) +

                            IF Attr([Dept])="700101" THEN

                                  CASE Attr([Sub Dept])

                                       WHEN "700201" THEN 2000

                                  END

                            ELSEIF Attr([Dept])="700201" THEN

                                  CASE Attr([Sub Dept])

                                       WHEN "701201" THEN 600

                                       WHEN "701202" THEN 400

                                       WHEN "701203" THEN 500

                                       WHEN "701204" THEN 300

                                       WHEN "701205" THEN 200

                                  END

                            END

                            • 11. Re: Aggregate and aggregate again?
                              Ashley Howard

                              If a sub dept name will never overlap, you could use just one long Case Statement on Sub Dept (example below), but I personally think the first version is cleaner.

                               

                              SUM([Sub Allocation]) +

                              CASE ATTR([Sub Dept])

                                   WHEN "700201" THEN 2000

                                   WHEN "701201" THEN 600

                                   WHEN "701202" THEN 400

                                   WHEN "701203" THEN 500

                                   WHEN "701204" THEN 300

                                   WHEN "701205" THEN 200

                              END

                              • 12. Re: Aggregate and aggregate again?
                                Ashley Howard

                                Was looking back at the string and missed your question about the sub-sub-departments.  Did you work it out?