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

# Calculate return rate

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

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

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