3 Replies Latest reply on Jan 12, 2016 12:55 AM by twelvetens

    Strange Filtering Calculation Question


      Been banging my head against this one for a while, and can't quite get it sorted, so hopefully someone with a finer mind than mine can help!


      I have a list of dated historical orders, with quantities, and costs per item...


      QtyCost Per ItemOrder Date


      The number of entries in the historical orders table above can obviously range from 0 up to a few hundred.


      The user wants to find out what the mean average price per item has been for the past X number of items purchased, across all historical orders, when sorted by order date (descending).


      So, for the historical orders above, if the user wanted to look at the last 350 orders, we'd be looking for...


      100 X 8.00 +

      200 X 7.00 +

      50   X 8.20     <---- 50 is used as opposed to 100, as there is only 50 remaining from the specified 350 after the previous two orders...


      / 350


      = 7.457




      This needs to be done as part of a calculation, as opposed to a looping script (if possible). I'm guessing this requires a recursive funciton of some sort?


      I've tried looking through various custom function sites, but I can't really articulate what I'm after above into a meaningful search term, so I'm a bit stuck!



        • 1. Re: Strange Filtering Calculation Question

          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 ]

          End If


          Exit Loop If [ $exit = 1 ]

          End Loop


          Set Variable [ $meancost ; Value: $runningtotal / $initialfind ]



          Exit Script [ Result: $meancost ]

          • 2. Re: Strange Filtering Calculation Question

            < 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)

            • 3. Re: Strange Filtering Calculation Question

              Thanks Mardikennedy.


              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...