Calculating Average of Average

[#1]

Hi

I have added animation to a bubble chart which animates on a dimension called 'Year'. Value in X-axis changes according to an expression which calculates the average of some values corresponding to a particular year. For example for Year '2001', value at X-axis is 100 (which is average of values for the year 2001). Similarly, for Year '2002', value at X-axis corresponds to average value for year 2002. But instaed, I want the average of all the values till 2002 (including the values for year 2001), not just the average of values corresponding to year 2002. Moving to Year '2003' in animation, average of all values from 2001 till 2003 should be calculated and set as X-axis values. This is equivalent to calculating average of average of values being calculated at every step.

How can I achieve this. Please help.. Thanks in advance..

[#2]

Nishant,

If I read this correctly, you are looking to have a type of running average.  Since you will be needing this over multiple dimensions, you really can't use the true "running" approach as you will produce an average of averages, which will, of course, be wrong.

The approach I came up with is this:

• Create a lookup table with the Year you want to animate (VizYear) on and all the years you want to average together
• Join that lookup table with your data
• Replace your current Year Dimension with VizYear

I put a simplified version of the concept below.  You will need to modify it to your dimensions, since you will have something more complex than this for a bubble chart.  Dumping this to a straight table and taking averages will show the math works.

load *
inline [Year, Category, Value
2001, A, 100,
2001, A, 125,
2001, A, 175,
2001, B, 200,
2001, B, 250,
2001, B, 400,
2002, A, 110,
2002, A, 150,
2002, A, 185,
2002, B, 250,
2002, B, 275,
2002, B, 350,
2003, A, 55,
2003, A, 65,
2003, A, 120,
2003, B, 150,
2003, B, 175,
2003, B, 200,
];
join
VizYear:
load *
inline [Year, VizYear
2001, 2001,
2001, 2002,
2002, 2002,
2001, 2003,
2002, 2003,
2003, 2003
];