10 Replies Latest reply on Jan 18, 2011 7:59 AM by Sabari Navaneethakrishnan

# Force Calculated Field to occur at a given level of a hierarchy and then roll-up

I am trying to create a calculation that is ALWAYS done at a particular level of the product hierarchy (i.e. Product Category) and then rolled up to higher levels of the product hierarchy.

Assume the following product hierarchy:

Product Type -> Product Group -> Product Category -> SKU

I want to 'force' the following formula to always occur at the Product Category level:

(Qty (current period) - Qty (last period)) * Avg Margin Dollars

...then roll up the answer (which is dollars) up the product hierarchy together with the rest of the Categories to Product Group and Product Type.

I can easily define the formula that I want but I cannot force the Calculated Field to always start at Category then be rolled up.

Any ideas?

• ###### 1. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

Sounds like you need a custom table calculation with advanced partitioning.

Can you provide about a dozen rows of sample data, and what you expect the display to be at the different hierarchy levels?

• ###### 2. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

Here is sample data for what Javier and I are looking for

Period    Product Type    Product Group    Product Category    SKU    Qty    Avg Margin Dollars

Current    A    1    W    1000    2    3

Last    A    1    W    1001    3    1

Current    A    1    X    1002    5    3

Last    A    1    X    1003    8    0.5

Current    A    2    Y    1004    1    6

Last    A    2    Y    1005    5    2

Current    A    2    Z    1006    6    4

Last    A    2    Z    1007    7    1

Current    B    3    C    1008    12    0.5

Last    B    3    C    1009    4    3

Current    B    3    D    1010    17    0.5

Last    B    3    D    1011    3    6

Current    B    4    E    1012    5    3

Last    B    4    E    1013    9    0.75

Current    B    4    F    1014    4    5

Last    B    4    F    1015    2    10

• ###### 3. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

Here is the sample data

Period    Product Type    Product Group    Product Category    SKU    Qty    Avg Margin Dollars

Current    A    1    W    1000    2    3

Last    A    1    W    1001    3    1

Current    A    1    X    1002    5    3

Last    A    1    X    1003    8    0.5

Current    A    2    Y    1004    1    6

Last    A    2    Y    1005    5    2

Current    A    2    Z    1006    6    4

Last    A    2    Z    1007    7    1

Current    B    3    C    1008    12    0.5

Last    B    3    C    1009    4    3

Current    B    3    D    1010    17    0.5

Last    B    3    D    1011    3    6

Current    B    4    E    1012    5    3

Last    B    4    E    1013    9    0.75

Current    B    4    F    1014    4    5

Last    B    4    F    1015    2    10

• ###### 4. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

Here is the Sample data

The expectation is that the calculation should be done at the product category level and when rolled up the levels the results should be the summation of the calculation result at the product category level and not the result of the calculation at the higher levels. Does this help?

• ###### 5. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

In the data you provided, Qty (current period) and Qty (last period) are on separate rows, and each have a Avg Margin Dollars. They can esaily by put on the same row to perform the calculation, but they would have different Avg Margin Dollars, would you like the Avg Margin Dollars to be averaged? or does that make sense?

With that sample data as the example source, can you make a table of what you want the display to be at the product category level?

• ###### 6. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

Here's the same sample data with desired display/output at each level of aggregation. Thanks for the assistance!

• ###### 7. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

There are a few different routes to accomplish what you are looking for, but I believe the simplest is create a worksheet for each view that you want to display, and then use a parameter to show only the sheet that you want to see (using containers on a dashboard). The attached demonstrates this.

I had tried using Tableau's built-in hierarchy, but I cold only detect if the current display was at the most detail level or not. I could not determine exactly what level or hierarchy was displayed when not at the most detailed level. Therefor, I did not see a way to set the partitioning based on the current hierarchy selected.

There may be other situations where using a change in hierarchy could display a different calculation.

• ###### 8. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

When the forum is fixed, I will be able to attach a file again.

• ###### 9. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

Here is the workbook

• ###### 10. Re: Force Calculated Field to occur at a given level of a hierarchy and then roll-up

Thanks Joe. Really appreciate the help