How to calculate an average of first 200 rows in a column from a table with 20000 rows.

[Vishal Sanghai]

Hello,

I want to create a horizontal line expression on a column to be applied as a limit to a scatter plot.

I want to model the expression for the horizontal line so that it uses an average of the first 200 rows only.

I tried playnig around with the last periods function in spotfire, but not getting a correct value for the line expression.

Any ideas

 

Thanks,

Vishal

[Khushboo Rabadia]

Not quite sure on your ask as there is no sample dxp provided. Based on the description, you can just use something like this: Avg(if(rowid()

[Vishal Sanghai]

Thanks a lot Khushboo.

I have attached the sample dxp file.

I tried the method you recommended but not getting the intended result.

I wanted to add a horizontal line based on custom expression (final offset Avg (first 200 SN_numbers)) to the Final_Offset_VLB_x vs SN_Number plot.

When I try your method, I get the custom expression but its not for the final offset avg of first 200 SN_Numbers. Its for the final offsetavg of first 200 Row ID. The Row ID and SN Number are different columns. The line expression doesn't allow me to select first 200 SN_Numbers over the Y axis although it allows me to select RowID(). Final offset Avg of first 200 RowID() = 21 while final offset Avg of first 200 SN_Number = 18.745.

Can you please let me know if its doable

 

Thanks,

Vishal

[Khushboo Rabadia]

Yes, you can use rowid() in horizontal line expression as it is spotfire function but cannot use any other column other than [Y] in expression. If you can use data function in your environment then it is doable dynamically or if you are interested in having action control button then it is doable with Iron Python script. But this is not doable with custom expression in lines & curves. Let me know if you want to explore these options.

[Vishal Sanghai]

I would like to explore those options. Maybe create an extra column based on averages of first 200 datapoints only and then use that column to create the horizontal line using value from data table.

 

Have not used data function or iron python script before so am really interested to know.

 

Thanks,

 

Vishal