7 Replies Latest reply: May 31, 2016 4:26 PM by Jean-Christophe GUILLERMIN

# Compute Rolling Average with multiple criteria

Hello,

I am trying to compute the production time per week per work center. Here is what the input file looks like:

 Work Center Week Article Production Order Reference Time A 1 toto O1 2 B 1 titi O2 4 A 1 tata O3 3 C 1 titi O4 4 A 2 toto O5 2 A 2 titi O6 4 B 2 tata O7 3 C 2 titi O8 2 C 3 toto O9 3 C 3 toto O10 4 A 3 titi O11 2 B 3 tata O12 5 B 3 titi O13 2

As I have 100's of production center, I would like to display only the one where the production of the week (sum of the reference time of the week for each work center) is less than 10% of the moving average of the last 2 week of this same production center (i.e. a quick way to identify a production center is producing less than what it should be).

I have build a table using group by agregating the data by week, but i am stuck there. Where should i start?

Rgs.

• ###### Re: Compute Rolling Average with multiple criteria

Is this something you want in the script or front end of the application?

• ###### Re: Compute Rolling Average with multiple criteria

Hello,

I was thinking doing the computation in the script, and then display only themost changing values in the front end.

• ###### Re: Compute Rolling Average with multiple criteria

May be like this:

Table:

Week,

Article,

[Production Order],

[Reference Time]

FROM

(html, codepage is 1252, embedded labels, table is @1);

AggrTable:

Week,

Sum([Reference Time]) as TotalWeek1

Resident Table

Group By [Work Center], Week;

Left Join (AggrTable)

Week + 1 as Week,

TotalWeek1 as TotalWeek2

Resident AggrTable;

Left Join (AggrTable)

Week + 2 as Week,

TotalWeek1 as TotalWeek3

Resident AggrTable;

Left Join (Table)

Resident AggrTable;

FinalTable:

RangeAvg(TotalWeek2, TotalWeek3) as RangeAvg,

If(TotalWeek1 < 0.90 * RangeAvg(TotalWeek2, TotalWeek3), 1, 0) as Flag

Resident Table;

DROP Tables AggrTable, Table;

The above script will flag the Work Center each week with a flag of 0 or 1. 1 is below the avg * 0.90 and 0 is above.

• ###### Re: Compute Rolling Average with multiple criteria

This should do the trick. Many thanks for your quick reply, thank you!

If you have time, what would it look like if I wanted to do directly to the front end?

• ###### Re: Compute Rolling Average with multiple criteria

May be try this:

If(Sum([Reference Time]) < RangeAvg(Above(Sum({<Week>}[Reference Time]), 1, 2)) * 0.90, 1, 0) * Avg(1)

With Work Center and Week as dimension

• ###### Re: Compute Rolling Average with multiple criteria

Hello,

What should I do when I hit the end of the year? (ie reach week 51/52)? How can I code the fact that I have to restart from week 1 for the following year?

I have the in raw file the date info from where I can extract week & year.