Flagging a process where the previous 3 week instances were the same

[Christopher]

I'm monitoring the run time of several different jobs and want to flag a job if I have 3 weeks in a row with the same flag. If a job did not run in a week I want to use the previous week as default.

I have a column called year median which takes the median for a job over a year and a week median which takes the median for a job over the week (using YEARWEEK function on the date [Year & Week])

I also have a previous flag column that uses SN(First([Control Flag]) OVER (Intersect(previous([Year & Week]),[Job ID])),[Control Flag]) to pull the previous week's flag for that job.

I use that in the Integer([Next Flag]!=[Control Flag #4]) to populate a 1 for the [Flag Change] column every time there is a change in the flag for a job then the previous week.

 

I've tried using [Flag Change] + sum([Flag Change]) OVER (Intersect(Previous([Year & Week],3),[Job ID])) to alert if = 0 but that only accounts for the week 3 behind the week it's evaluating for and not the weeks in between. 

I've also tried[Flag Change] + sum([Flag Change]) OVER (Intersect(Previous([Year & Week],3),[Job ID]))+ sum([Flag Change]) OVER (Intersect(Previous([Year & Week],2),[Job ID])) + sum([Flag Change]) OVER (Intersect(Previous([Year & Week],1),[Job ID])) to alert if = 0 but this nulls out if there is no data for the job in one of those weeks is blank.

 

Thank you in advance!

 

 

 

 

Spotfire example.xlsx

[David Boot-Olazabal]

Hi Christopher,

I'm not sure if I quite understand your data set, as I don't see any formulas in there. It would be easier to understand if your dxp was shared.
But with the above description, do you want another flag column that can act as a trigger for an alert? And you're looking for a way to set up that flag column?

If so, when you have that flag, how would you trigger an alert? Would you be using our Alerting Framework for Spotfire® you can find here? Or do you have a different way to trigger alerts?

Kind regards,

David

[Christopher]

Hello David,

I want another flag column that can act as a trigger for an alert whenever I have 4 instances in a row with the same control flag. (trying to use the western electric method to detect processing time drifts).

I have tried this for this formula for the alert flag but if one of the weeks is missing data it defaults to null:

case  
when (([Flag Change] + sum([Flag Change]) OVER (Intersect(Previous([Year & Week],3),[Job ID])) + sum([Flag Change]) OVER (Intersect(Previous([Year & Week],2),[Job ID])) + sum([Flag Change]) OVER (Intersect(Previous([Year & Week],1),[Job ID])) And ([Control Flag]!='Normal') then 'Alert' & " " & [Control Flag] end

 

On my attached data I would want to flag yearweek 20243 for Jobs A&C and an flag 20245 for Job C

 

 

The alerting framework would be my next step in this process looking very similar to what is shown in the link you included although I've never downloaded an add on for Spotfire before.

I expect this add on would still need an alert flag column for notifications? The rules on my actual data will be alerts on 8 points above or below median. Alert if 4 of the 5 pervious data points where above or below 1std dev.

Spotfire example - Processing Time Control.dxp

[Olivier Keugue Tadaa]

Hi Christopher

To summarize,

You would like to detect if there is an anomaly after 3 consecutive weeks.  An anomaly is when the week's median for a specific job is above or below the year's median of that job.
Therefore I did the following.

1- Pivot your data to have them per week and not per day 
2-  calculate an integer control flag  with the logic of putting 0 if it is below, and 1 if it is above (you  can choose any value you want since you know how to use  them later)
3- calculate the sum of the last previous control flags  like this:
Flag_calc.  ::: Sum([Control Flag Int]) OVER (Intersect(LastPeriods(3,[YearWeek]),[Job]))
4-  calculate the alert column. in my case, I did this : 
Alert :::
case [Flag_calc]
when 3 then true
when 0 then TRUE
else FALSE
end
We still need to  solve the missing value problem, but this is a matter of data prep that can be managed by adding a time/week dimension to fill the missing values per job and then applying the missing value policy (e.g. previous value like you want)
Here are some screenshots and the DXP attached

Spotfire-example-Processing-Time-Control-otadaa.dxp

[Olivier Keugue Tadaa]

to give you an idea, this part can be done using a Dimension (yearweek-job) data table containing two columns (job, yearweek) which is a cartesian product of both dimensions (see the second tab from the attached)
1- add column from that dimension table and select full outer join  (or right outer join)

2- replace empty values like this ...

I hope this will solve your problem. Please let us know.

 

Spotfire example -tadaa.xlsx

[Olivier Keugue Tadaa]

Hi Christoper, were you able to try the above suggestions?