2 Replies Latest reply: Aug 2, 2018 10:51 PM by Sibin Jacob.C C RSS

    Calculate return rate

    Wanyun Yang

      My table has one field Type with values 'quotes', 'orders', 'returns', another field Amount with sales amount. I'm trying to calculate the return rate, with some function like:

      SUM({<Type={'returns'}>}Amount)/(SUM({<Type={'quotes'}>}Amount)+SUM({<Type={'orders'}>}Amount))


      But I want to put Type as a filter and achieve:

      1. If you select quotes or orders or returns or (quotes, orders) , it will show 0 or nothing.

      2. If you select (quotes, returns), it will show SUM({<Type={'returns'}>}Amount)/SUM({<Type={'quotes'}>}Amount)

      3. If you select (orders, returns), it will show SUM({<Type={'returns'}>}Amount)/SUM({<Type={'orders'}>}Amount)

      4. If you select (quotes, orders, returns), it will show SUM({<Type={'returns'}>}Amount)/(SUM({<Type={'quotes'}>}Amount)+SUM({<Type={'orders'}>}Amount))


      Is that possible? Thanks in advance!

        • Re: Calculate return rate
          Sunny Talwar

          May be this

           

          If(GetSelectedCount(Returns) > 0 and (GetSelectedCount(Orders) > 0 or GetSelectedCount(quotes) > 0), Sum({<Type *= {'returns'}>} Amount) / (Sum({<Type *= {'quotes'}>} Amount)+SUM({<Type *= {'orders'}>} Amount)), 0)

          • Re: Calculate return rate
            Sibin Jacob.C C

            Please use the below expression.

             

            if( Type='returns' or SubStringCount(GetFieldSelections(Type),'returns') = 0 ,0

            ,

            if(SubStringCount(GetFieldSelections(Type),',')=2,

            SUM({<Type={'returns'}>}Amount)/(SUM({<Type={'quotes'}>}Amount)+SUM({<Type={'orders'}>}Amount))

            ,

            if(SubStringCount(GetFieldSelections(Type),'quotes')=1,

            SUM({<Type={'returns'}>}Amount)/SUM({<Type={'quotes'}>}Amount),

            if(SubStringCount(GetFieldSelections(Type),'orders')=1,

            SUM({<Type={'returns'}>}Amount)/SUM({<Type={'orders'}>}Amount)

            ))))

             

             

            For Testing:

            Create List box Type, put the below expression in a text box

             

             

            if( Type='returns' or SubStringCount(GetFieldSelections(Type),'returns') = 0 ,0

            ,

            if(SubStringCount(GetFieldSelections(Type),',')=2,'All 3 selected',

            if(SubStringCount(GetFieldSelections(Type),'quotes')=1,'quotes return formula',

            if(SubStringCount(GetFieldSelections(Type),'orders')=1,'orders return formuls'

            ))))

             

            Thanks,

            Sibin