7 Replies Latest reply on Apr 9, 2015 5:37 AM by Yuriy Fal

# How to create a moving average based on lower level of hierarchy?

I currently am working with a data set where I need to create a moving average based on levels of a hierarchy.  In the hierarchy I am currently working on I need to do a different average than what tableau normally does. Standard averaging is taking the lowest level data and averaging it to get the highest level of displayed hierarchy.

Rather than using an average of the lowest parts, I am hoping to average at each level of the hierarchy. So in the table below, fruit is made up of an average of strawberry orange and apple.  Food is made up of an average of the already calculated averages of fruit, vegetable and deliciousness.

I've tried table calculations to no avail, any help would be greatly appreciated.

Level1Level2Level3

Food

FruitStrawberry
FoodFruitOrange
FoodFruitApple
FoodVegetableSquash
FoodVegetableBroccoli
FoodVegetableSpinach
FoodDeliciousnessNutella
FoodDeliciousnessMore Nutella
• ###### 1. Re: How to create a moving average based on lower level of hierarchy?

Hi Ryan.

Table calc may help.

Yuri

1 of 1 people found this helpful
• ###### 2. Re: How to create a moving average based on lower level of hierarchy?

Yuriy,

Is there a way to create a calculation that will allow more than one level of exposed Hierarchy?  For example, if I expose level 3 the calculations no longer work.  The actual dataset I am working against has 4 levels of depth, and I am hoping to be able to average to the highest level, and possibly then average that layer (For instance if Level 1 had food and electronics as categories, one level up would be the average of those)

• ###### 3. Re: How to create a moving average based on lower level of hierarchy?

Hi Ryan,

This is possible.

But a table calc logic become complicated,

so one needs to be carefull enough to test it

with each pill combination possible in a view.

Please find the attached wb as an example.

Yours,

Yuri

• ###### 4. Re: How to create a moving average based on lower level of hierarchy?

Thanks Yuriy!  You're a life saver.  Might I ask where you've learned so much about tableau and table calcs?  I can't find very man resources for self help.

• ###### 5. Re: How to create a moving average based on lower level of hierarchy?

Hi Ryan,

Tableau Training and Tutorials

Table Calcs intro can be found here:

http://www.tableau.com/learn/tutorials/on-demand/table-calculations-0

But first, I strongly recommend to watch and understand these concepts:

Understanding Pill Types | Tableau Software

Aggregation, Granularity, and Ratio Calculations | Tableau Software

And my favorite source of inspiration is the blog by Tableau Zen Masters

Jonathan Drummey and Noah Salvaterra:

Drawing with Numbers | Thoughts on data visualization and Tableau

Yours,

Yuri

• ###### 6. Re: How to create a moving average based on lower level of hierarchy?

I know this is a while later.  But I'm attempting to figure out how to make the second calculation in your workbook work.  Essentially I am trying to move up the hierarchy and use the averages from one level lower to get the higher average.

Averages of level 3 = level 2 average.  Averages of level 2(which were averages of level 3) equal the level 1 average.  But I need to be able to do this in hierarchys that can be up to 15 deep.  Is this possible?

Essentially it would be the window average of a window average.

• ###### 7. Re: How to create a moving average based on lower level of hierarchy?

Hi Ryan,

As been mentioned by Jonathan Drummey somewhere at this forum,

a hierarchy and a table calc isn't a perfect pair :-)

Hopefully, with a new v9 Level-Of-Details (LOD) calculations in place,

one could write such a calc (average of average ... of average in your case)

nested down to a deepest level in the hierarchy.

Please find the attached v9 wb.

Hope it helps.

Yours,

Yuri