topic Re: RANGE FILTERS in Visualization and Usability

RANGE FILTERS

Wayne_101 — Sat, 16 Nov 2024 19:30:03 GMT

Re: RANGE FILTERS

andoryuu — Sun, 08 Dec 2019 23:08:23 GMT

In your table, under "Data" --> "Columns" expand your "RC" field and uncheck "Include Null Values" like I have highlighted in my screenshot below. This will remove those rows from your table. Apply this change to any fields that you want to behave in this fashion.

Include null values unchecked

Re: RANGE FILTERS

Rodj — Mon, 09 Dec 2019 01:54:39 GMT

If it is a single loaded number (i.e. you have calculated it previously or in the load script) then in your dimension you can simply check whether it is in your range and hide the nulls. So if I have two columns, ID and measure, the measure logic is simply sum(measure), the dimension logic can be:

if(Measure >= $(vMin) and Measure <= $(vMax), ID)

if however you want to calculate your measure dynamically you need to let the Qlik engine know how you want to group the data, so your calculated dimension now needs an aggregation at the correct level for it to work:

if(Aggr(sum(Measure), ID) >= $(vMin) and Aggr(sum(Measure), ID) <= $(vMax),ID)

The exact answer will vary a bit depending on how and when you are calculating your measure and there's probably a better way of doing it than what I've come up, but this seems to work for me.

Cheers,

Rod

Re: RANGE FILTERS

Wayne_101 — Mon, 09 Dec 2019 03:43:20 GMT

That option has already been unchecked but still same result , when i
include 2nd column it doesn't bring the customer_id in required ranges
rather it shoes null values with one of them.

Re: RANGE FILTERS

Wayne_101 — Mon, 09 Dec 2019 03:45:52 GMT

Working on @Rodj solution , hope it works . Will get back on this in a while

Re: RANGE FILTERS

Wayne_101 — Mon, 09 Dec 2019 07:58:11 GMT

hi,

@Rodj

i'm trying following solution for measures :

=if(Aggr(sum(MO)>= $(vMinMO)) and Aggr(sum(MO) <= $(vMaxMO)),Customer_id)
=if(Aggr(sum(RC) >= $(vMinRC)) and Aggr(sum(RC) <= $(vMaxRC)),Customer_id)

and for dimension which is customer id i'm using following logic:

=if(MO>= $(vMinMO) and MO<= $(vMaxMO)
or If RC>= $(vMinRC) AND RC<= $(vMaxRC),Customer_id)

empty table pops up which seems like above logic has needs some tuning .

Re: RANGE FILTERS

Rodj — Mon, 09 Dec 2019 08:36:03 GMT

I think your measures can just be "sum(MO)" and sum(RC)etc, the only limits we need to apply are to the dimension:

=if(Aggr(sum(MO)>= $(vMinMO), Customer_id) and Aggr(sum(MO) <= $(vMaxMO)Customer_id), Customer_id)

Notice the Customer_id I've added into the Aggr statements, Customer_id is what we are aggregating by and it is the level at which you want the measure summed so everything lines up.

Hopefully that does it!

Re: RANGE FILTERS

Wayne_101 — Thu, 12 Dec 2019 18:56:31 GMT

hi,

Re: RANGE FILTERS

andoryuu — Mon, 09 Dec 2019 13:07:50 GMT

@Wayne_101 can you post your QVF file?

Re: RANGE FILTERS

Wayne_101 — Thu, 12 Dec 2019 19:02:41 GMT

HI,

Re: RANGE FILTERS

andoryuu — Mon, 09 Dec 2019 19:38:52 GMT

You need to change your customer_id dimension to be the following:

=aggr(Only({<[CUSTOMER_ID]={"=((SUM(MO) >= $(vMinMO) and SUM(MO) <= $(vMaxMO)) or (SUM(RC) >= $(vMinRC) AND SUM(RC) <= $(vMaxRC)))"}>}[CUSTOMER_ID]),[CUSTOMER_ID])

Then set your MO and RC fields to simply be SUM(MO) and SUM(RC). This should solve the issue. Changes applied to your sample attached.

Re: RANGE FILTERS

Rodj — Mon, 09 Dec 2019 20:14:26 GMT

Sorry @Wayne_101 , it occurred to me late last night that that my solution was only going to work for one measure and you wanted it to work for more than one, I'm trying to do too many things at once! Looks like @andoryuu has it sorted for you now though. Good luck!

Re: RANGE FILTERS

Wayne_101 — Tue, 10 Dec 2019 10:29:24 GMT

hi,

@andoryuu

the above shared logic doesn't work .

slider doesn't filter the data in ranges as seen in below images.

lets say :

eg :i want to see my customer who have MO between 200 and 300 plus having RC between 1000 and 3000

Re: RANGE FILTERS

Saryk — Tue, 10 Dec 2019 10:42:31 GMT

This :

= If (MO>= $(vMinMous) AND MO<= $(vMaxMous) or If RC>= $(vMinRC) AND RC<= $(vMaxRC),Customer_ID)

should be this (easily expanded) :

= If ( (MO>= $(vMinMous) AND MO<= $(vMaxMous)) AND (RC>= $(vMinRC) AND RC<= $(vMaxRC)), Customer_ID)

Also, you don't need the filter on your measures, only on the dimensions.

Re: RANGE FILTERS

Saryk — Tue, 10 Dec 2019 10:49:49 GMT

I posted an answer but it was reported as spam for some reason.

Your dimension's IF condition doesn't work, here's a working version of your file.

Re: RANGE FILTERS

Wayne_101 — Tue, 10 Dec 2019 12:09:34 GMT

@Saryk

it works perfectly fine , just one last thing how can i make it work with count(distinct customer_id)

what you suggested for dimension

=if(
(MO >= $(vMinMO) and MO <= $(vMaxMO)) and
(RC >= $(vMinRC) AND RC <= $(vMaxRC))
,CUSTOMER_ID)

what you suggested for Measure

=if(Aggr(sum(RC)>= $(vMinRC), CUSTOMER_ID) and Aggr(sum(RC) <= $(vMaxRC),CUSTOMER_ID),sum(RC))

logic i want to use for dimension :

=if(
(MO >= $(vMinMO) and MO <= $(vMaxMO)) and
(RC >= $(vMinRC) AND RC <= $(vMaxRC))
,count(distinct CUSTOMER_ID))

Re: RANGE FILTERS

andoryuu — Tue, 10 Dec 2019 14:16:14 GMT

It works based on your logic. You have them specified as or statements. everything you circled falls into EITHER the MO OR the RC band. If you want it to be the intersection, just change OR to AND.

Re: RANGE FILTERS

andoryuu — Tue, 10 Dec 2019 14:18:04 GMT

@Saryk Filtering on measures was removed in my reply back.

Re: RANGE FILTERS

Saryk — Tue, 10 Dec 2019 14:34:24 GMT

My bad, I didn't check the measures in the file I sent you back.

You can actually change them to =sum(MO) and =sum(RC) because the filtering is already done in the dimension.

Re: RANGE FILTERS

Wayne_101 — Tue, 10 Dec 2019 14:37:20 GMT

Please look into my above use case , what if i want count distinct of
customers falling between my rc and mo ranges