Averaging a number makes it negative?

[Anonymous]

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

[Gysbert_Wassenaar]

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.

[Anonymous]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[Anonymous]

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. 

[swuehl]

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.