topic Re: Wrong sum in QlikView

Wrong sum

Fri, 14 Feb 2014 09:16:43 GMT

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

Re: Wrong sum

Gysbert_Wassenaar — Fri, 14 Feb 2014 09:25:58 GMT

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.

Re: Wrong sum

kouroshkarimi — Fri, 14 Feb 2014 09:35:00 GMT

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?

Re: Wrong sum

Fri, 14 Feb 2014 09:37:01 GMT

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.