topic Re: Errors when comparing values due to formatting in QlikView

Errors when comparing values due to formatting

Anonymous — Thu, 13 Mar 2014 16:06:05 GMT

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

Please advise

Thanks

Hasnaa

Re: Errors when comparing values due to formatting

PrashantSangle — Thu, 13 Mar 2014 16:20:34 GMT

Hi,

What is KPI1?

It is string or column that contain any value for comparison?

Re: Errors when comparing values due to formatting

Carlos_Reyes — Thu, 13 Mar 2014 16:21:27 GMT

Perhaps you're loading the values incorrectly.

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%

Hope it helps!

Re: Errors when comparing values due to formatting

Anonymous — Thu, 13 Mar 2014 16:49:11 GMT

KPI1 is a calculated value which is displayed as number to 2 dp. it is based on calculations from pre existing value loaded in the data model

Re: Errors when comparing values due to formatting

Anonymous — Thu, 13 Mar 2014 16:52:15 GMT

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)?

Thanks

Hasnaa

Re: Re: Errors when comparing values due to formatting

Carlos_Reyes — Thu, 13 Mar 2014 17:18:47 GMT

Review the enclosed example. It may help you discover what could be wrong.

Regards!