topic Re: Averaging a number makes it negative? in QlikView

Averaging a number makes it negative?

Anonymous — Fri, 07 Jun 2013 15:46:51 GMT

What am I missing? Shouldn't the average of a number be the number? Why is it negative?

Re: Averaging a number makes it negative?

Gysbert_Wassenaar — Fri, 07 Jun 2013 18:53:37 GMT

I think it's a corrupted table. If I export the data to excel and oad that in a new document the averages are correct. If I export the data to a qvd and load that in a new document then the averages are incorrect. I suggest you create a new document and reload the original data.

Re: Averaging a number makes it negative?

Anonymous — Fri, 07 Jun 2013 18:56:45 GMT

Thank Gysbert. I'm getting the data out of a QVD. Any suggestions?

Re: Averaging a number makes it negative?

Gysbert_Wassenaar — Fri, 07 Jun 2013 19:02:05 GMT

First try loading the qvd in a new qlikview document. If that doesn't work recreate the qvd from the original data because it's probably the qvd that's corrupt. If that's not possible try storing it as csv after loading it with the STORE command and see if the resulting csv file is correct. If it is then try using that the load the data. If the solves the problem you can next try storing that table again in a qvd and see if loading that qvd also works.

Re: Averaging a number makes it negative?

Anonymous — Fri, 07 Jun 2013 20:15:32 GMT

Thank you Gysbert. That's definitely helpful.

The only remaining issue is how does it get corrupted in the first place.

I have traced it to the conversion process. I have a QVX that retrieves the data as a string so I have to surround it with the Num# function. Here's a sample value

=avg(Num#('3053740032'))

that comes -1.2412273e+009.

Things get fixed once you use Num instead on Num#. Don't understand that.

Re: Averaging a number makes it negative?

swuehl — Fri, 07 Jun 2013 22:51:39 GMT

You probably don't need to use the number interpretation function here, QV should read this in as a number automatically.

If you do need the num#() function, try using a format specifier:

=avg(Num#('3053740032','#'))

This should fix your issue, I believe it's related to this discussion.