Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 CenterWeekArticleProduction OrderReference Time
A1totoO12
B1titiO24
A1tataO33
C1titiO44
A2totoO52
A2titiO64
B2tataO73
C2titiO82
C3totoO93
C3totoO104
A3titiO112
B3tataO125
B3titiO132

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.

1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

7 Replies
sunny_talwar

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

Not applicable
Author

Hello,

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

sunny_talwar

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.

Not applicable
Author

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?

sunny_talwar

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

Not applicable
Author

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

Not applicable
Author

Solution found using weekname() function