How to improve the calculation of the max value on a line chart in the presence of fluctuations

[Michael Daiy]

Hello,

I am trying to dynamically determine the"maximum" value of a columnusing a Calculated Value within a Text Area. However, the issue is that the value I am looking for is not the absolute maximum. Rather, I need to find the maximum of the curveformedbythe Line Chart as indicated with the red arrow in the attached picture. I have been struggling with coming up with a way to accomplish this. Does anyone have any ideas, scripting or otherwise

Thank you

[Gaia Paolini]

Hi

From the picture, it looks like you are not dealing with outliers, but are looking to improve the calculation of the max value in the presence of fluctuations.

One possible solution to this is in the attached dxp.

Create a column using a call to loess, with a smoothing parameter between 0 and 1, which you can define via a document property (loessSpan) in a text area.

TERR_Real("output

[Michael Daiy]

Hello Gaia,

 

First, thank you so much for your informative response. Unfortunately, I am not able to open the file you have attached as your version is incompatible with mine (7.1 and 7.7 respectively). I am new to Spotfire, so I am having a couple of issues implementing your expression. 

 

I went to Edit->Data Function Properties->Expression Functions->New and added in the expression from your answer. I named it Smoothing->Ok->Close.

 

Next, I tried to follow your instructions about adding a column. This is where I had trouble. I went to the functions section and selected Smoothing from the list and clicked Insert Function. Then I inserted the x and y columns followed by a value between 0 and 1 within the parenthesis separated by commas. Like this: 

 

Smoothing( [xColumn], [yColumn], .5)

 

The OK button in the bottom right of the Insert Calculated Column window is grayed out and the Sample Result field shows "#Error"

 

Where do I go from here

[Michael Daiy]

Please excuse my inexperience. I realize now that the [x] and [y] are where I was intended to put my column names. My issue is I am not sure what I am supposed to enter for the input(s)

[Gaia Paolini]

Hi

I am attaching snapshots.

From 'SpotfirePage' you see, top right, a text area where you insert a value into the document property loessSpan. Default is 0.75.

From 'CalculatedColumn' you see that you already have a function you can use, no need to define an expression function. The function is TERR_Real, available in the menu. It is from within this function that you call Loess. So this is a simple calculated column, sorry if the terminology was confusing.

At this point you have at your disposal a new column, which I called ySmoothed, that you can calculate the Max on, using the dynamic calculated value (bottom right of 'SpotfirePage'). If you cannot do it in your version, creating a calculated column Max([ySmoothed]) would do the job.

Gaia

[Gaia Paolini]

p.s.

my test file hadtwo columns, [x] and [y]. These are the columns I used for inputs. You can adapt to your column names. You don't need to use the document property loessSpan, inserting the number directly would work. I just added the document propertyso you can see the effect of different smoothing parameters. I am attaching the test file so you have all the bits.

Gaia

[Michael Daiy]

Excellent! I have this working with test data. However if the x values are dates, this does not work. Is there any solution to this or do these parameters strictly need to be Reals

[Gaia Paolini]

hi

 

yes the parameters to a curve smoother need to be numbers. 

 

However you may be able to turn your date column into a number, and then use that as the TERR_Real function input. For instance, if you create a calculated column such as:

 

DateDiff("dd",[xdate],DateTimeNow())

 

this would give you a number, the difference in days ("dd") between now (DateTimeNow()) and your date column (here called [xdate]). Depending on the granularity of the date column you might choose a difference e.g. in months, or minutes, etc. The function DateDiff is pre-built and available to select in the menu.

 

Another alternative: number of days between a fixed date (01/01/1970) and your date column:

 

DateDiff("dd",Date(1970,1,1),[xdate])

 

Gaia