topic Re: Ranges in QlikView

Ranges

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Ranges

sibin_jacob — Tue, 14 Aug 2018 06:50:19 GMT

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

Re: Ranges

olivierrobin — Tue, 14 Aug 2018 06:53:22 GMT

hello

you can also use the intervalmatch() function

Re: Ranges

soniasweety — Tue, 14 Aug 2018 06:57:23 GMT

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

AmountRange:

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

Re: Ranges

soniasweety — Tue, 14 Aug 2018 06:57:51 GMT

Thanks how can i implement this with above condition?

Re: Ranges

olivierrobin — Tue, 14 Aug 2018 06:59:51 GMT

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

Re: Ranges

sibin_jacob — Tue, 14 Aug 2018 07:18:58 GMT

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