nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Moving average on expression

fporcher

Hi, 
 
How work moving average : 
I've a dataset with 2 col 
1st day of month - value 
<blockquote class='ipsBlockquote' >
3.0 2006-09-01 00:00:00 
81.31667375564575 2006-10-01 00:00:00 
240.28496837615967 2006-11-01 00:00:00 
41.51111149787903 2006-12-01 00:00:00 
31.26666784286499 2007-01-01 00:00:00 
... 
45.4578113315779 2009-02-01 00:00:00 </blockquote>
 
I want a bar chart 
x : date 
y : the moving average 12 month past 
 
 
How to write the expression (I'm lost) 
 
Thanks 
F.P.

Find more posts tagged with

Comments

mwilliams

Hi F.P.,

Can you explain this a little more?

fporcher

<blockquote class='ipsBlockquote' data-author="mwilliams">Hi F.P., 
 
Can you explain this a little more?</blockquote>
 
I've one value by Date (1st day of month) 
I want draw i.e. 
<pre class='_prettyXprint _lang-auto _linenums:0'>
2008-03-01 12
2008-02-01 11
2008-01-01 10
2007-12-01 9
2007-11-01 10
2007-10-01 13
2007-09-01 11
2007-08-01 12
2007-07-01 18
2007-06-01 13
2007-05-01 17
2007-04-01 12
2007-03-01 5
2007-02-01 11
2007-01-01 10
</pre>
For 2008-01-01 = sum of (values 2007-01-01 to 2008-01-01) div by 13 
(10+9+10+13+11+12+18+13+17+12+5+11+10)/13 
For 2008-01-02 = sum of (values 2007-02-01 to 2008-02-01) div by 13 
 
Better will be 
sum ( for month 2007-01-01 to 2008-01-01) (value div by number of day in month)/13 
 
(10/31) 
+(9/31) 
+(10/30) 
+(13/31) 
+(11/30) 
+(12/31) 
+(18/31) 
+(13/30) 
+(17/31) 
+(12/30) 
+(5/31) 
+(11/28) 
+(10/31) 
/ 13

mwilliams

F.P.,

So, you want the moving average for up to 13 months?

fporcher

<blockquote class='ipsBlockquote' data-author="mwilliams">F.P., 
 
So, you want the moving average for up to 13 months?</blockquote>
Yes, and how to generalyze to N month ...

mwilliams

F.P.,

If you just put '13' in the expression box, it'll show the running average of the last 13 values or how many are before the current one up to 13.

If you could set it up to do "N months", how would you determine N?

fporcher

<blockquote class='ipsBlockquote' data-author="mwilliams">F.P., 
 
If you just put '13' in the expression box, it'll show the running average of the last 13 values or how many are before the current one up to 13.</blockquote>
 
neither one nor the other. It displays the current month 
 
 
<blockquote class='ipsBlockquote' data-author="mwilliams">If you could set it up to do "N months", how would you determine N?</blockquote>
N is fixed in the chart (this is not a variable). 
See attachment base on sampleDatabase

fporcher

I create a table with agreagation, in this case, it work

but not in chart

mwilliams

F.P., 
 
If you just have one series, it works. If you create a computed column in your dataSet that repeats the value of row["SOMME"] and do the aggregation on the first series using the new computed value for the second series, it works. But if you do the aggregation on the second series in any case, it seems to just follow the first series. This definitely looks like a bug to me. Please log it at <a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/reportabug.php'>BIRT: Reporting Bugs and Requesting Enhancements</a>. Be sure to include a good description, screenshots, and the sample report design you posted on here. Please include any bug info in here for future reference. Thanks. 
 
In the following screenshot, you can see that if you use a computed column called SOMME2, and use it as the second series with the aggregations, the preview shows it correctly. However, when I run it, it is not correct.

turissa

Hello, I have a similar problem and the solution here works for me on a table, but I need a 13 period moving average on a crosstab and the aggregate function MOVINGAVE is not possible. I have two years of values with 13 periods each (network rail periods). I need a script to give me a moving average for each year. 

I have found a javascript expression which gives me a result but it's not quite right. The crosstab is grouped by Year and Period

movAveArray.push(data["IMO_MTIN"]);
 
var movingSum = 0;
 
for ( var i=movAveArray.length-1; i>=0; --i ) {
movingSum = movingSum + movAveArray;
}
 
movingSum / movAveArray.length;

I also have the following in the beforeFactory script:
movAveArray = new Array();

This is the sample data and column 3 (Excel) holds the correct result I am looking for:
 

Period

MTIN Miles

Excel Moving Average 13

1

<div>
<div>
<div>2,548,011.70</div>
</div>
</div>

#N/A

2

10,135,680.00

#N/A

3

7,597,118.30

#N/A

4

3,101,974.00

#N/A

5

55,700.00

#N/A

6

5,039,990.00

#N/A

7

100

#N/A

8

233.3

#N/A

9

174,430.00

#N/A

10

5,710.00

#N/A

11

412

#N/A

12

5,039,990.00

#N/A

13

100

2,592,265.33

1

<div>
<div>
<div>172,855.00</div>
</div>
</div>

2,409,560.97

2

55,800.00

1,634,185.58

3

6,044.00

1,050,256.79

4

55,800.00

815,935.72

5

340,000.00

837,804.95

6

500

450,151.87

7

1,684,990.70

579,758.85

8

5,425,721.00

997,104.05

9

1,309,912.00

1,084,448.82

10

85,175.00

1,090,561.52

11

2,905.00

1,090,753.28

12

412

703,093.44

13

113,637.30

711,827.08

newbie321

IMHO, If you are doing SMA(x), where x is the time period, then it will be just a matter of time before someone asks you to do EMA or rolling volatility over arbitrary time period as well as run-ups, draw-down, etc etc... When I was doing these things few years ago, I found that creating a custom aggregator gave the most power as well as flexibility. At that time Jason Weathersby (miss him very much although the expert level of current BIRT moderators/evangelists is top notch here) helped me a lot in understanding how to do it. 
 
I believe if you look through older eclipse/birt forums then you will be able to find a number of references. If my memory does not betray me, I ended up doing an extension to birt, so when you load up birt, these custom aggregations come naturally via the Birt's front end. 
 
I realize my answer does not solve your problem explicitly but at least it gives you a general direction of the vector of the solution that you might want to explore.
 
Hope it helps,