Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wanyunyang
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
sibin_jacob
Contributor III

Re: Calculate return rate

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

2 Replies

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)

sibin_jacob
Contributor III

Re: Calculate return rate

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

Community Browser