Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

If Condition Incorrect result

I am getting incorrect results when using the if statement to determine the dimension for a bar graph.

The script in question is as shown below;

=if(Benefit_Limit < 50000 , 'BELOW-50K',
if(Benefit_Limit >= 50000 and Benefit_Limit < 100000, '50-100K LIMIT',
if(Benefit_Limit >= 100000 and Benefit_Limit < 300000, '100-300 LIMIT',
if(Benefit_Limit >= 300000 and Benefit_Limit < 500000, '300-500 LIMIT',
if(Benefit_Limit >= 500000 and Benefit_Limit < 1000000, '500K-1M LIMIT', 'Over 1M')))))

 

There are values for each range but I'm getting the following output.

j0nimost1_0-1593858962742.png

I have not limited the width or anything of the sort. I can't figure out the reason for this behavior. Kindly assist



Labels (2)
7 Replies
Highlighted
MVP
MVP

what is the expression you are using?

Highlighted
Contributor II
Contributor II

It's in the question-- but here you go;

 

=if(Benefit_Limit < 50000 or IsNull(Benefit_Limit), 'BELOW-50K',
if(Benefit_Limit >= 50000 and Benefit_Limit < 100000, '50-100K LIMIT',
if(Benefit_Limit >= 100000 and Benefit_Limit < 300000, '100-300 LIMIT',
if(Benefit_Limit >= 300000 and Benefit_Limit < 500000, '300-500 LIMIT',
if(Benefit_Limit >= 500000 and Benefit_Limit < 1000000, '500K-1M LIMIT', 'Over 1M')))))

Highlighted

Hi,

 

What is your dimension an measure?? Convert this chart into table and check. How you are testing the value that it is correct or not?

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
MVP
MVP

It is perhaps something not related to this expression. The potential issue could be with your data. If you could share a sample data has the issue - we may have a look and try to help. In the mean while, you can simplify expression like:

=if(Benefit_Limit < 50000 or IsNull(Benefit_Limit), 'BELOW-50K',
if(Benefit_Limit >= 50000 and Benefit_Limit < 100000, '50-100K LIMIT',
if(Benefit_Limit >= 100000 and Benefit_Limit < 300000, '100-300 LIMIT',
if(Benefit_Limit >= 300000 and Benefit_Limit < 500000, '300-500 LIMIT',
if(Benefit_Limit >= 500000 and Benefit_Limit < 1000000, '500K-1M LIMIT', 'Over 1M')))))

Contributor II
Contributor II

Sample Data; in the database it's saved as nvarchar

Benefit Limit 

1000000
1000000
1000000
50000
50000
50000
100000
100000
50000
NULL
50000
50000
35000
100000
50000
50000
1000000
1000000
100000
75000
75000
50000
100000
100000
100000
100000
50000
100000
100000
50000
50000
50000
100000
100000
100000
100000
50000
35000
50000
50000
50000
50000
1000000
50000
50000
50000
50000
50000
50000
50000
50000
30000
50000
75000
50000
50000
50000
75000
5000000
75000
35000
35000
35000
75000
75000
35000
35000
50000
50000
50000
35000
50000
50000
100000
100000
100000
100000
50000
75000
75000
75000
75000
50000
50000
50000
50000
50000
50000
75000
75000
75000
50000
50000
100000
100000
150000
75000
NULL
500000
500000
75000
75000
75000
75000
75000
50000
50000
50000
50000
150000
150000
150000
150000
75000
75000
100000
100000
100000
100000
500000
500000
10000000
10000000
100000
2500000
NULL
500000
500000
35000
75000
75000
50000
50000
50000
130000
35000
75000
100000
75000
75000
50000
50000
20000
50000
50000
50000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
100000
100000
100000
100000
150000
100000
100000
100000
100000
100000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
150000
100000
100000
100000
150000
75000
75000
75000
150000
150000
150000
150000
150000
150000
100000
100000
150000
150000
150000
150000
150000
150000
75000
150000
150000
75000
75000
75000
75000
75000
75000
75000
75000
75000
300000
300000
150000
150000
150000
50000
75000
75000
75000
75000
75000
75000
75000
75000
50000
75000
75000
75000
75000
75000
50000
50000
100000
50000
35000
35000
50000
35000
35000
50000
50000
35000
35000
35000
35000
100000
100000
100000
100000
70000
100000
35000
35000
35000
35000
100000
100000
100000
100000
50000
100000
100000
12000
100000
50000
50000
50000
50000
150000
150000
50000
50000
200000
150000
200000
200000
50000
150000
150000
150000
150000
35000
200000
200000
100000
200000
200000
150000
150000
150000
150000
150000
150000
50000
75000
150000
50000
150000
35000
50000
50000
200000
150000
150000
150000
35000
200000
200000
50000
150000
35000
150000
150000
150000
150000
50000
50000
50000
50000
50000
150000
150000
150000
150000
150000
200000
150000
150000
150000
50000
35000
150000
200000
150000

Highlighted
Contributor II
Contributor II

Benefit Limits from the database are nvarchar

 

Here is a sample;

1000000
1000000
1000000
50000
50000
50000
100000
100000
50000
NULL
50000
50000
35000
100000
50000
50000
1000000
1000000
100000
75000
75000
50000
100000
100000
100000
100000
50000
100000
100000
50000
50000
50000
100000
100000
100000
100000
50000
35000
50000
50000

Highlighted
MVP
MVP

With my simplified expression for your sample data I am getting like:

=if(Benefit_Limit < 50000 or Benefit_Limit='NULL', 'BELOW-50K',
if(Benefit_Limit < 100000, '50-100K LIMIT',
if(Benefit_Limit < 300000, '100-300 LIMIT',
if(Benefit_Limit < 500000, '300-500 LIMIT',
if(Benefit_Limit < 1000000, '500K-1M LIMIT', 'Over 1M')))))

tresesco_0-1594042604572.png