Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

soniasweety
Valued Contributor III

Ranges

Hi All,

i want to create the filter with ranges like below  how can i build this?

i have a column : TotalAmount    based on this i need to create a Range filter

ranges: $0 to 100K  and 100K-1M, 1M to 10M, 10M+

how can i do this?

Thanks

Sony

Tags (2)
6 Replies
Highlighted
sibin_jacob
Contributor III

Re: Ranges

Create new column in the same table using the below script

if(TotalAmount >10000000,' 10M+',

if(TotalAmount >1000000 and TotalAmount <=10000000 ,' 1M to 10 M',

if(TotalAmount >100000 and TotalAmount <=1000000 ,' 100K - 1M',

'$0 to 100K'

))) As Range_Filer

olivierrobin
Valued Contributor II

Re: Ranges

hello

you can also use the intervalmatch() function

soniasweety
Valued Contributor III

Re: Ranges

if i want to create one more column like this how can i do this?

AmountRange:

less than 1, 1-500K, 500K-5M, 5M+

soniasweety
Valued Contributor III

Re: Ranges

Thanks how can  i implement this with above condition?

olivierrobin
Valued Contributor II

Re: Ranges

you will have to create a table with 3 columns

min value

max value

label of the range

then use intervalmatch() to join your data with this table

have a look at qlik documentation, see Matching intervals to discrete data

sibin_jacob
Contributor III

Re: Ranges

if(TotalAmount >5000000,' 5M+',

if(TotalAmount >500000 and TotalAmount <=5000000 ,' 500K to 5 M',

if(TotalAmount >=1 and TotalAmount <=500000 ,' 1 - 500K',

'lessthan 1'

))) As Range_Filer1


or you can give specifically, in the below case, zero values are not in any Range


if(TotalAmount >5000000,' 5M+',

if(TotalAmount >500000 and TotalAmount <=5000000 ,' 500K to 5 M',

if(TotalAmount >=1 and TotalAmount <=500000 ,' 1 - 500K',

if(TotalAmount <1 ,

'lessthan 1'

))) ) As Range_Filer1