Wrong sum

[#1]

Hello;

I am loading a spreadsheet that has been saved as a text file to speed up the download.

Because of that the trailing minus is not respected.

I can solve the problem apply this:

num(if(right([Inv Amount],1) = '-',0-left([Inv Amount],len([Inv Amount])-1),[Inv Amount])) as [Inv AmountNum]

The numbers are correctly being retrieved.

The problem comes when I apply a sum with the field in question in an expression.

This is were I lost it.

No idea why this is happening.  Hope you do.

Many thanks;

Kristel

[Gysbert_Wassenaar · Accepted solution]

Perhaps you want a sum(distinct [Inv AmountNum]). You seem to have a lot of duplicate records. You can see this if you add another expression to your straight table: count([Inv AmountNum]). You'll see there are 249 records with an Inv AmountNum value of 649.35.

[kouroshkarimi]

Gysbert is correct, you're getting 161688.5 because its 249*649.35. Unfortunately for your sanity QlikView is giving you the correct answer! You should consider your data model again, maybe distinct load your text file?

[#4]

Thanks, this is where it is going wrong.

I am now loading each tab one by one to understand where the duplication occurs.

Fresh eyes looking at your own stuff is always helping.

Again many thanks.