I am having errors when comparing values. The comparison is not working.
I am actually calculating a KPI value and displaying it as an expression formatted as Number, 2 decimal places in a chart. I also have a target KPI value to which I am comparing the calculated KPI value and displaying a smiley of color green, orange or red. The target KPIs are loaded from a CSV file (values are to 2d.p with a "%" at the end.
Sample:
KPI_name, Threshold_1, Threshold_2
KPI1, 0.49%,0.50%
This means that when KPI1 <=0.49%, smiley color = green
0.49% < KPI1 <= 0.50%, smiley color = orange
KPI1 > 0.50% , smiley color = red
The values calculated in the expression might be to several decimal places (e.g 0.5033565899). When displaying it in the chart, I am showing it as a number to 2 d.p (formatting done on Numbers tab in chart wizard) "= 0.50%" .
To display the smiley, I am using the following:
if ( KPI1<= Threshold_1, green_smiley,
if (KPI1<= Threshold_2, orange_smiley, red_smiley))
when evaluating this comparison, it is showing the red smiley instead of the orange one. I think it is the formating. I have tried to compare the values using something like round(KPI1,0.01) <= round(Threshold_1,0.01), it still doesnt work
when displaying the threshold value is a table box, the value displays as 0.005%. But I guess this should not be the issue
If you upload .50%, thats equal to .005... so if your expressions value is higher than .005 you'll get the red smiley.
If you mean that .50% should be equal to .5 which then you need to review how are you loading or transforming that number so that it represents .5 = 50%, not .005 = .5%
It is string or column that contain any value for comparison?
Great dreamer's dreams never fulfilled, they are always transcended. Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
If you upload .50%, thats equal to .005... so if your expressions value is higher than .005 you'll get the red smiley.
If you mean that .50% should be equal to .5 which then you need to review how are you loading or transforming that number so that it represents .5 = 50%, not .005 = .5%
is there a way of formatting the values in the script during loading? in the chart, when calculating KPI1 and displayign it as % to 2 dp, it is already evaluating as 0.00503356589 I guess
Is it ok to compare values like if (round(KPI1,0.01) <= round(Threshold_1,0.01), ...) ?
Whats is the difference between functions Num, Num#, and round(xxx,0.01)?
