How to calculate the average between two dates for every item in the table?

[Andreas Komocar]

I have a table with multiple values for each material. I need to calculate the average between two dates(user deined, and i know how to make it filter) for each material in a table. I dont know where to start and how to do it. I have attached a screenshot so you can view few lines of my data. Any help is appreciated. Thank you

[Gaia Paolini]

Please can you upload a sample dataset as text file. This will enable somebody to have real context to answer your question. Make sure the data can be public.

[Andreas Komocar]

I attached some sample data as txt file.

[Gaia Paolini]

There could be different ways to do this, depending on what you want to display.

I am assuming you want a cross table with the results.

One solution could be to use document properties and a calculated column: 

Create a text area to select a start date and an end date, to be stored into document properties startDate and endDate.

Then calculate a column called e.g. [included] as:

Days(DateDiff([Date],Date('${startDate}')))>=0 and Days(DateDiff([Date],Date('${endDate}')))<=0

Create a cross table, in the Data tab choose Limit data using expression and set it to [included]=True

and set the vertical axis to the Material and the cell value to Avg([Date]) 

An alternative solution (simpler if you don't mind filtering out the rest of the data) would be to use filters.

Here you select dates by adding a text area with the Date filter.

Then in your cross table Data tab, you choose Limit data using filterings.

Attached a sample dxp with the two alternative suggestions.

Note that when importing your data I needed to choose a different 'culture' than en-GB (my default) to make sure Value was read as Real, given the comma decimal separator.  Make sure Date is of type Date and not String. 

[Andreas Komocar]

Appreciate your reply. I will try what you suggest. Thank you. But sadly i can't open your example files, seems i have older version, see attached image

[Gaia Paolini]

I am not sure I can find an older version to try, I am on 12.1.1. The description should be enough to reproduce hopefully.

I am going to attach a couple of screen shots. (The 'Available Date Range' in the text area is simply a couple of calculated values, Min([Date]) and Max([Date]) but not strictly part of the solution).

[Gaia Paolini]

I've got a version that should open in Spotfire 11.4

[Andreas Komocar]

Tnx for your effort. I did manage to solve it. Now i have to make further analysis