Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Is this something you want in the script or front end of the application?
Hello,
I was thinking doing the computation in the script, and then display only themost changing values in the front end.
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.
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?
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
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
Solution found using weekname() function