RANGE FILTERS

[Wayne_101]

Hi

[andoryuu]

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 unchecked

[Rodj]

 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

[Wayne_101]

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.

[Wayne_101]

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

[Wayne_101]

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 .

[Rodj]

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!

[Wayne_101]

hi,

[andoryuu]

@Wayne_101  can you post your QVF file?

[Wayne_101]

HI,