Calculate return rate

[wanyunyang]

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!

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

[sibin_jacob · Accepted solution]

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