3 Replies Latest reply on Jun 29, 2016 9:10 AM by coherentkris

    Mode (mathematical) calculation for products/line items

    JamesTorr

      Folks a little question here, so I'd like to work out a most frequent order quantity (mode) for my invoice line items and to view that in the product page.

       

      I understand that this would be the right calculation but I'm not experienced enough with SQL calcs to be able to plug in the correct data. Can somebody help me or at least steer me in the right direction?

       

       

      // Mode ( ValueList ; Value; Cnt )

      //

      // valueList    :  A return separated list of values. values can be text or number

      // value         : Iterim value that passes the current mode candidate to the next recursive call of the function.

      // Cnt            : The count of the items of the currently nominated value

      //

      Let ( c = ValueCount ( FilterValues ( ValueList ; GetValue ( valueLIst ; 1 ) ) ) ;

                Case ( IsEmpty ( ValueList ) ; Value ; // return current data in Value if list is empty

                          c > Cnt ; Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; GetValue ( ValueList ; 1 ) ; c ) ;

                          Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; Value ; Cnt )

                         ) // case

            ) // let