Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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

View solution in original post

3 Replies
Gysbert_Wassenaar

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

View solution in original post

kouroshkarimi
Creator III
Creator III

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
Author

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.