topic Re: Compute Rolling Average with multiple criteria in App Development

Compute Rolling Average with multiple criteria

Thu, 26 May 2016 15:06:04 GMT

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?

Thank you for your help

Rgs.

Re: Compute Rolling Average with multiple criteria

sunny_talwar — Thu, 26 May 2016 16:14:02 GMT

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

Re: Compute Rolling Average with multiple criteria

Thu, 26 May 2016 16:37:48 GMT

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

sunny_talwar — Thu, 26 May 2016 17:12:31 GMT

May be like this:

Table:

LOAD [Work Center],

Week,

Article,

[Production Order],

[Reference Time]

FROM

[https://community.qlik.com/thread/218486]

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

AggrTable:

LOAD [Work Center],

Week,

Sum([Reference Time]) as TotalWeek1

Resident Table

Group By [Work Center], Week;

Left Join (AggrTable)

LOAD [Work Center],

Week + 1 as Week,

TotalWeek1 as TotalWeek2

Resident AggrTable;

Left Join (AggrTable)

LOAD [Work Center],

Week + 2 as Week,

TotalWeek1 as TotalWeek3

Resident AggrTable;

Left Join (Table)

LOAD *

Resident AggrTable;

FinalTable:

LOAD *,

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

Thu, 26 May 2016 18:19:01 GMT

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

sunny_talwar — Thu, 26 May 2016 18:51:14 GMT

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

Mon, 30 May 2016 04:20:24 GMT

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.

Many thanks for your support!

Rgds

Re: Compute Rolling Average with multiple criteria

Tue, 31 May 2016 20:26:32 GMT

Solution found using weekname() function