Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Wayne_101
New Contributor III

RANGE FILTERS

Hi

 

 

 

 

 

 

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: RANGE FILTERS

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. 

 

View solution in original post

24 Replies
Highlighted
andoryuu
Contributor III

Re: RANGE FILTERS

Hi @Wayne_101 ,

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 uncheckedInclude null values unchecked

Highlighted
Luminary
Luminary

Re: RANGE FILTERS

 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

Highlighted
Wayne_101
New Contributor III

Re: RANGE FILTERS

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.
Highlighted
Wayne_101
New Contributor III

Re: RANGE FILTERS

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

Highlighted
Wayne_101
New Contributor III

Re: RANGE FILTERS

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)error_new.PNG

 

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

Highlighted
Luminary
Luminary

Re: RANGE FILTERS

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!

Highlighted
Wayne_101
New Contributor III

Re: RANGE FILTERS

hi,

 

 

 

 

Highlighted
andoryuu
Contributor III

Re: RANGE FILTERS

@Wayne_101  can you post your QVF file?

Highlighted
Wayne_101
New Contributor III

Re: RANGE FILTERS

HI,