Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
hello
you can also use the intervalmatch() function
if i want to create one more column like this how can i do this?
AmountRange:
less than 1, 1-500K, 500K-5M, 5M+
Thanks how can i implement this with above condition?
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
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