Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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)
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