Ranges

[soniasweety]

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

[sibin_jacob]

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]

hello

you can also use the intervalmatch() function

[soniasweety]

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]

Thanks how can  i implement this with above condition?

[olivierrobin]

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]

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