Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I have not limited the width or anything of the sort. I can't figure out the reason for this behavior. Kindly assist
what is the expression you are using?
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')))))
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
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')))))
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
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
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')))))