topic Calculation result not as expected in QlikView

Calculation result not as expected

Fri, 18 Feb 2011 23:46:07 GMT

Hi All,

This is my first post, so be easy on me. 🙂

I have a strange probleem that I think should be solved quite easily.

I have the following data as an example:

ID length

1 180

2 345

3 180

I tried the following expression in a textbox:

1. sum(length) = 705 <= correct and as expected

2. count(length) = 3 <= correct and as expected

3. avg(length) = 0.16319444 <= incorrect, expected 705/3 = 235

4. sum(length)/count(length) = 0.16319444 <= incorrect, expected 705/3 = 235

What should I do to get the correct value '235' ? I dont quite understand why it calculates 0.16319444 instead of 235.

Thank you in advance.

Calculation result not as expected

Fri, 18 Feb 2011 23:59:29 GMT

Hi there,

I think there is something wrong when you load your date, because I create your scenario and both expresions: avg(length) and sum(length)/count(length) gives me 235 for result

Can you upload your file?

I attached mine.

Regards!

Calculation result not as expected

Sat, 19 Feb 2011 00:20:29 GMT

Hi Gabriela,

I attached my file.

Thank you for your reply.

Kind Regards.

Calculation result not as expected

Sat, 19 Feb 2011 00:54:03 GMT

Hi,

here is a solution:

avg(num#(frame1,'#'))

it seams like frame1 is not formated as a number

Hope this helps!

Regards!

Calculation result not as expected

Sat, 19 Feb 2011 01:15:39 GMT

Thanks Gabriela. Your solution works like a charm!

Could you explain a bit for me? I would like to understand a little bit more. Frame1/2/3 is an interval and not a number, so what kind of value is 0.16319444?

Thanks a lot!

Calculation result not as expected

jonathandienst — Sat, 19 Feb 2011 14:53:59 GMT

Here's why (from your sample):

Your script created a dual value that had a text representation in minutes that totalled 1925 when added, and a numeric value of day fraction. ie for ID 110, 180 minutes = 0.125 of 24 hours. 0.16319444 = 1.336806 / 6 which is correct.

IMO the total of 1925 is a bug as you should not be able to total the text representation, as shown by the inability to total the expression Text(frame1).

Column 4 shows how you can get numeric minutes from the value.

Hope that clears it up.

Jonathan

Calculation result not as expected

Sun, 20 Feb 2011 13:45:37 GMT

ok that clears up thing! Thank you for your explanation.