Either Relationships or calculations using ExecuteSQL can reference records with the same stock symbol for a computed range of dates for the last 7, 30, etc time intervals in order to return an average of the ValueTraded field.
Do you want to try this with an SQL query or self join relationships created in Manage | Database | Relationships?
Do you just need to display the average values or will you need to use these values in additional calculations?
Thanks for answering
I would like to try both if possible but I am only familiar with the self join. Would I need SQL installed or is it just the syntax I would be using and which I have a bit of familiarity with?
Just the average values would be needed.
It would take more time to describe both methods than I am willing to spend doing so. And thus I am asking your to choose.
ExecuteSQL() is a built in function in FileMaker 12. It is not available in earlier versions. While powerful, this function can be very difficult to use due to very uninformative error results when you get the SQL syntax wrong, it is best used in the current version by those who already have a basic knowledge of SQL queries.
Then self join please
Set up a self join relationship:
StockData::Stock Symbol = StockData|SameSymbol::Stock Symbol
StockData and StockData|SameSymbol are both Tutorial: What are Table Occurrences? with the same data source table.
Define a summary field in StockData, sAvgValue, that returns the average of your value traded field.
On your StockData layout, put a one row portal to StockData|SameSymbol. define this portal filter:
StockData|SameSymbol::Date > Get ( CurrentDate ) - 7
Put sAVGValue in the portal row and it should show the average over the preceding 7 days.
Repeat this process but, using -30, -45, -60 in the portal filter to get totals for other time intervals.
You can also use a global field instead of an explicit number and then you can get different totals for any time interval the user selects, but then you should use this relationship:
StockData::Stock Symbol = StockData|SameSymbol::Stock Symbol AND
StockData::gGlobalField X StockData|SameSymbol::anyfield
so that the portal filter will automatically update whenever the value of gGlobalField is changed.
Please note that these values are display only. The values cannot be accessed by a script or calculation. A different approach can be used if such is needed.