Selectable number line items averaged
Example: I have 100+ line item entries and I'm wondering if it's possible to have an average of only a given amount of entries (example the last 20). This number would have to be editable.
Such can be scripted. It might be possible to do without the script, but we'd need to know more about what made those records the "last 20" (all have a common value, the 20 that sort last in a specified sort order...) How you want to disply this average also can be a factor in how you implement this:
Go To Record/Request/Page [last]Loop Exit Loop If [$I = YourTable::GlobalNumbItemsField] Set Variable [$Sum ; Value: $Sum + YourTable::YourNumberField] Set Variable [$I ; value $I + 1 ] Go To Record/Request/Page [Previous; Exit after last]End LoopAt this point, what you do depends on what you want to do with this average. If you just need to display it on the current report or to pop up the average in a dialog box, use:
Set variable [$$Avg ; Value: $Sum/$I ] //To display on a report, put it on your layout as <<$$Avg>> and add a refresh window step after it to make sure that it displays properly.
If you need to retain this value for other users even after closing the file, use set field instead of set variable and store the value in a field, you can even create a record in a separate table and use it to store the value if you need to.
that's perfect Phil, exactly what I need. Thank you!
Retrieving data ...