To add some clarity here, I have got this working in a script, by passing the relevant parameters as values, which returns the mean average as a script result, but I need this to be a calculation...
# Find the mean average of the values of ListA multiplied by corresponding values in ListB
# Only uses as many 'rows' in the lists as defined by the value defined by 'FindValue'
# Define Variables:
# FindValue is single number value.
# ListA is a commaseperated list of numbers
# ListB is a commaseperated list of numbers
# Script Exits with the Mean Average
Set Variable [ $initialfind ; Value: gsp (1) ]
Set Variable [ $listqty ; Value: Substitute ( gsp (2) ; "," ; "¶" ) ]
Set Variable [ $listcosts ; Value: Substitute ( gsp (3) ; "," ; "¶" ) ]
Set Variable [ $counter ; Value: 0 ]
Set Variable [ $runningtotal ; Value: 0 ]
Set Variable [ $find ; Value: $initialfind ]
Set Variable [ $counter ; Value: $counter +1 ]
Set Variable [ $qty ; Value: GetValue ($listqty ; $counter) ]
Set Variable [ $cost ; Value: GetValue ($listcosts ; $counter) ]
If [ GetAsNumber ( $find ) ≥ GetAsNumber ( $qty ) ]
Set Variable [ $runningtotal ; Value: $runningtotal + ($qty * $cost) ]
Set Variable [ $find ; Value: $find - $qty ]
Set Variable [ $runningtotal ; Value: $runningtotal + ($find * $cost) ]
Set Variable [ $exit ; Value: 1 ]
Exit Loop If [ $exit = 1 ]
Set Variable [ $meancost ; Value: $runningtotal / $initialfind ]
Exit Script [ Result: $meancost ]
< I need this to be a calculation >
Might be time for some smoke and mirrors. Why a calc? Maybe because the user actually prefers a printed page and therefore it's easier to 'see' a list. If not, then a popover can make for a nice, 'tactile' UI and the scripting is relatively invisible.
If that 'printed list' style output is required, then the user will still need to set parameters, eg how many units OR what time period. (Aside: multiple units, or single unit enquiries? If hm units, then implies single item, depending on the kind of stock they're talking)
At any rate, at the time of entering the parameter, you'd use your smoke and mirrors. ie, run a script based on the work you've already done, to calc the relevant values and temporarily store them in a 'results' table, or a global field, with repetitions. (I'd probably opt for the gfield.) You just need to make sure that your report layout can draw upon those stored values and display them usefully. (This can be a bit challenging; depends upon your particular circumstances.)
The actual time taken to do a scripted process rather than an actual calc is, from a user's pov, often invisible so it's just working out the display options. Note: if scripting calc'd results then SQL might be practical. (depending on actual requirements and resources available)
I had gotten a scripted solution in place, but there were a myriad if issues with that approach, so in the end I have managed to write my own recursive function which does the job - got there in the end!
If anyone is interested, I'm happy to post the solution here...