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

# Aggregate and aggregate again?

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?

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?

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

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?

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

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?

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?

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?

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?

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?

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

• ###### 9. Re: Aggregate and aggregate again?

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?

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?

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?

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