1 Reply Latest reply on Nov 12, 2013 12:47 PM by philmodjunk

    Conditional averages calculation

    ultranix

      Title

      Conditional averages calculation

      Post


           Same joined table discussion, same solution of INVOICES and LINES table here at the center stage, different question.

           Let's say 1 invoice contains 12 line items (that are stored in lines), Layout is based on Invoices, and portal that is based on lines is placed on layout to add items to the Invoice.

           What I want to calculate - average price of each item, but i want to omit/exclude 2 max (highest price) and 2 min (lowest price) items.

           So, basically, it would look like this.

           1. Item 1 $20
           2. Item 2 $22
           3. Item 3 $16
           4. Item 4 $35
           5. Item 5 $44
           6. Item 6 $21
           7. Item 7 $24
           8. Item 8 $47
           9. Item 9 $41
           10. Item 10 $27
           11. Item 11 $32
           12. Item 12 $38

           So, items #3 and #1 have the lowest prices and must be omitted, same could be said about items #8 and #5, that have highest prices and average of 8 remaining items is 30.

        • 1. Re: Conditional averages calculation
          philmodjunk

               Use a new occurrence of lines, Lines|Sorted, and set up the same relationship, but specify a sort order in the that sorts them by the price field.

               Then a calculation field, cIDListAvg, with this calculation:

               Let ( IDList = List ( Lines|Sorted::__pkLinesID ) ;
                       MiddleValues ( IDList ; 3 ; ValueCount ( IDList ) - 4 ) )
                     )

               Will return the list of IDs for the lines records, but with the smallest two and largest two values omitted.

               Now you can add yet another occurrences of Lines:

               Invoices::cIDListAvg = Lines|Avg::__pkLinesID

               and Average ( Lines|Avg::price )

               will return the average price of all but the smallest and largest two prices.