Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How can i sum the following column

i have a column which has filed 'Amount' as followes:

Amount

80'899.15
37'787.46
37'787.46
13'895.00
33'924.67
1'799.23
3'598.46
501'242.88
1'503'728.66

When is use sum function sum(Amount) it gives value 0

Can anyone one explain how to resolve this problem

9 Replies
awhitfield
Partner - Champion
Partner - Champion

I would hazard a guess that Qlikview thinks the values are TEXT due to the ' being used as a separator, remove the character in the load script and add it again using the number formatting.

Andy

arulsettu
Master III
Master III

KeepChar(Amount,'0123456789') as Amount

now use sum(Amount)

krishna20
Specialist II
Specialist II

Hi,

The blow link may helps you

Remove characters before a special character

krishna20
Specialist II
Specialist II

Hi,

you can also try this if you have other special characters in your field data.

SubField(SubField(SubField(SubField(SubField(Amount,'/'),'.'),'&'),'('),')') as Amount;

tyagishaila
Specialist
Specialist

Hi,

Convert your Amount value from Text to number.

arulsettu
Master III
Master III

if it is solves your problem please mark it as correct answer.

bumin
Partner - Creator II
Partner - Creator II

just replace the sepecial thousand separator with nothig

num(replace (Amount,chr(39),''))    as    Amount

krishna20
Specialist II
Specialist II

Hi,

try this

LOAD * ,SubField(SubField(SubField(SubField(SubField(Amount,'/'),'.'),'&'),'('),')') as TEST_AMOUNT INLINE [

Amount

80'899.15

37'787.46

37'787.46

13'895.00

33'924.67

1'799.23

3'598.46

501'242.88

1'503'728.66

];

sasiparupudi1
Master III
Master III

PurgeChar(Amount,Chr(39)) as Amount