Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Wrong sum

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.

Sum issue.jpg

This is were I lost it.

No idea why this is happening.  Hope you do.

Many thanks;

Kristel

1 Solution

Accepted Solutions

Re: Wrong sum

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.


talk is cheap, supply exceeds demand
3 Replies

Re: Wrong sum

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.


talk is cheap, supply exceeds demand
kouroshkarimi
Contributor III

Re: Wrong sum

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?

Not applicable

Re: Wrong sum

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.

Community Browser