Finding the low Peaks on a line chart

[Cristian Tibrea]

Hello,

I have an interesting topic, that I kind of solved it partially. 

I have a line chart with a shape that is similar to sinus and I would like to create a trigger (a 1) every time the signal peaks down (or up). I created a calculated column for that (using LastPeriods) and I get some of the peaks but I am missing some. Maybe the interval is wrong (40), I tried a different numbers but this was my best guess. The line is ROP_FT (purple).

Here is my column:

case  WHEN 
[ROP_FT]=Min([ROP_FT]) over (LastPeriods(40,[EPOCH])) then 1 else 0 END

Is it possible to refine the query, maybe. not sure how. Many thanks

[Cristian Tibrea]

I was thinking at something else.

Is it possible to calculate (% maybe) the difference between Max and Min peaks for a time interval (maybe 2 min). This would probably help identify the dysfunction. Thank you.

[David Boot-Olazabal]

Hi Christian,

Could you elaborate a bit more about the other variables? And do you want a trigger, each time the purple line is diverting from a straight line? Because that seems to be always the case in your screenshot.
Also, it would be great if you could share a dataset and/or your dxp file so we can look into it more in depth.

Kind regards,

David

[Cristian Tibrea]

Hi David, sorry for the late reply, I was on vacation. I attached the .dxp file and the data table is LIC query (581_303BH) and the interval is the one in the previous screenshot (April 30 from 7pm to 7:20pm). The idea for this project is to identify a drilling dysfunction that would make ROP to fluctuate (as a sinusoidal wave). If I can catch the peaks (max or min) for this behavior then we can create a trigger if we have 5 peaks in 30 sec for example. Otherwise, will not be important.

Thank you for trying to look into it. I hope we can find a solution.

581-303BH-LIC query.dxp

[Olivier Keugue Tadaa]

Hi Cristian

Can I make this suggestion? 

Could you consider looking for a "local" outlier in the sense that you want to identify high or low values during the last n periods (you can find the right value for n later as explained in the next paragraphs)
First, calculate the following columns 

• the min, ROPBetweenRows_MIN:::min([ROPBetweenRows]) OVER (LastPeriods(${Periods},[DATE_TIME]))
• the max , ROPBetweenRows_MAX:::MAX([ROPBetweenRows]) OVER (LastPeriods(${Periods},[DATE_TIME]))
• the average , ROPBetweenRows_AVG::::AVG([ROPBetweenRows]) OVER (LastPeriods(${Periods},[DATE_TIME]))
• the standard deviation : ROPBetweenRows_STDEV:::StdDev([ROPBetweenRows]) OVER (LastPeriods(${Periods},[DATE_TIME])). 

where ${Periods} is a Document Property

A graphical presentation shows you this. To adjust these curves, you can change the ${Periods} value (via a text area).

Then you add the z-score on the plot like this 

• avg([ROPBetweenRows]) as [value], 
• Sum(([ROPBetweenRows] - [ROPBetweenRows_AVG]) / [ROPBetweenRows_STDEV]) as [z-score]

 

The z-score values are more normalized and help you decide when to trigger an anomaly.

The same approach can be adopted to calculate a different metric.

I hope this helps.

THE DXP is attached for any further help

581-303BH-LIC query-otadaake.dxp

Edited June 18 by Olivier Keugue Tadaa

[Tomas Jurczyk]

Hello.

I worked recently on a very related problem and I have a data function that might work very well for this usecase as well. It is based on a bit different approach than in the previous answer. I will try to use my approach and let you know with the results.

Thanks

Tomas

[Cristian Tibrea]

Thank you, Olivier, for your help. It looks interesting. I would also like to see Tomas's approach to this using the data function.

Tomas, I am looking forward to your approach.

Thank you everyone for help.

[Olivier Keugue Tadaa]

While waiting , here is the next step of the previous approach

Add a calculated column: z-Score :::: ([ROPBetweenRows] - [ROPBetweenRows_AVG]) / [ROPBetweenRows_STDEV]

 

and these custom expressions on your line chart.

• avg([ROPBetweenRows]) as [value], 
• Sum(if(Abs([z-Score])>${zscoreMAX},1,0)) as [trigger]. // trigger when the z-score absolute value  is higher than a max-limit

where ${zscoreMAX}, is a document property controlled in a text area via a property control 

Playing with the ${zscoreMAX} and the ${Periods} values will certainly provide an acceptable solution

Use the zoom slider in the attached example to see how it works 

581-303BH-LIC query-otadaake.dxp

 

Edited June 19 by Olivier Keugue Tadaa

[Cristian Tibrea]

I will look into it tomorrow, thank you Olivier.

[Cristian Tibrea]

Thank you, Olivier, for your support. With the zscore we get triggers too many times even though the signal doesn't have a sinusoidal shape. I have something in excel that works a bit better. We identified the sinusoidal shape of the signal and compare it with the entire dataset. When there is a similarity it will trigger (red marks). I was wondering if we can do something similar in Spotfire. I attached some screenshots with the results from excel.

[Tomas Jurczyk]

I am very sorry for the late reply. I tried my approach but unfortunately, my procedure cannot be applied directly to your data (it is true, it was a bit different use-case as well). There will be some customization needed to my script to achieve this.

Regards

Tomas