2 Replies Latest reply on Sep 19, 2011 8:42 AM by Matty_1

    Selectable number line items averaged



      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.

        • 1. Re: Selectable number line items averaged

          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]
             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 Loop
          At 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.

          • 2. Re: Selectable number line items averaged

            that's perfect Phil, exactly what I need.  Thank you!