Scaled Values Filter - Qlik Sense

[Anonymous]

Hi Everyone,

I was wondering if anyone could help please.

I need to create a filter which will allow me to filter my values on a table.

I have an average cost which is calculated with the following expression

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))

from the result of this expression I need to create a filter for the following values.

Between       £0 - £500

Between £1000 - £2000

Between £2000 - £3000

Between £3000 - £4000

Between £4000 - £5000

Between £5000 - £7000

Between £7000 - £9000

Anything above £9000

I am just not quite sure how I go about this.

Any help would be much appreciated.

Many Thanks

Kind Regards

Wendy

[brunobertels]

Hi Wendy

Have a look to this post it will help you

https://community.qlik.com/message/1269003#1269003

You have 2 way of doing your requierment :

Using Class() function but as i see that you do not use the same scale : 500 , or 1000 or  2000 , class function will not be the solution in your case

Then use bucket :

If(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 500,

'Between       £0 - £500',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 1000,

'Between       £500 - £1000',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 2000,

'Between       £1000- £2000',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 3000,

'Between       £2000- £3000,

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 4000,

'Between       £3000- £4000,

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 5000,

'Between       £4000- £5000,

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 7000,

'Between       £5000- £7000,

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count)) < 9000,

'Between       £7000- £9000',

'Anything above £9000'

))))))))

regards

Bruno

[Anonymous]

Thanks Bruno,

This is great but please could you help me into where I put it to create a Filter in Qlik Sense.

I have tried to put it into my load script but my script is very intense so I am struggling to know where in the script to put it.  Can I add it somewhere in an expression?

Many Thanks

Kind Regards

Wendy

[beck_bakytbek]

Hi Wendy,

for you filter, look at this blog,here i used buckets:

https://community.qlik.com/thread/257523

i hope that helps

Beck

[brunobertels]

Hi Wendy

As your mesure is a set analysis , you can't add it in the script and create a dimension .

but in backend you can do it like this :

Select Graph :

then Filter panel :

Clik on the FX menu :

Add your mesure :

If(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 500,

'Between       £0 - £500',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 1000,

'Between       £500 - £1000',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 2000,

'Between       £1000- £2000',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 3000,

'Between       £2000- £3000',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 4000,

'Between       £3000- £4000',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 5000,

'Between       £4000- £5000',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 7000,

'Between       £5000- £7000',

if(

(sum ({<DocType= {'InvoiceID'}>}Overall)- sum ({<DocType= {'[Credit ID]'}>}Overall)) / (Sum (Count))< 9000,

'Between       £7000- £9000',

'Anything above £9000'

))))))))

Name your filter pane

Regards

Bruno

[Anonymous]

Hi Bruno,

I tried that but unfortunately it comes back with invalid dimension.

Not quite sure what I am doing wrong.

Kind Regards

Wendy