# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## 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!

1 Solution

Accepted Solutions
Contributor III

## 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

2 Replies
MVP

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

Contributor III

## 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