Qlik Community

Qlik Sense Cloud Discussions

Highlighted
Luka
New Contributor III

qlik sense formula to add numbers in the same field

Hi,

I have a column in my table with several numbers separated by space

Is there a way to sum these numbers using a formula?

Example: 29 55 26.5 109

Tried multiple searches but no results, any help is appreciated.

Thanks

Labels (2)
2 Solutions

Accepted Solutions
MVP
MVP

Re: qlik sense formula to add numbers in the same field

In script you can use Subfield() to separate out the numbers and add them up. However, a quick shortcut and tricky workaround comes into mind; you can replace the space with '+' sign and evaluate it using dollar expansion like:

=$(=Replace('29 55 26.5 109', ' ','+'))

 

Luka
New Contributor III

Re: qlik sense formula to add numbers in the same field

So this is how i fixed it 🙂 

Thank you Treseco for the guidance !!

IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',1)),0,subfield([FREIGHT INVOICE VALUE],' ',1))+
IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',2)),0,subfield([FREIGHT INVOICE VALUE],' ',2))+
IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',3)),0,subfield([FREIGHT INVOICE VALUE],' ',3))
7 Replies
MVP
MVP

Re: qlik sense formula to add numbers in the same field

In script you can use Subfield() to separate out the numbers and add them up. However, a quick shortcut and tricky workaround comes into mind; you can replace the space with '+' sign and evaluate it using dollar expansion like:

=$(=Replace('29 55 26.5 109', ' ','+'))

 

miskinmaz
Contributor II

Re: qlik sense formula to add numbers in the same field

Hi,

 

subfield('29 55 26.5 109',' ',1)+ subfield('29 55 26.5 109',' ',2)+subfield('29 55 26.5 109',' ',3)+subfield('29 55 26.5 109',' ',4)

if it is always 4 digit number. But it is a good idea to generate a new field having all the values at row level from backend.

Luka
New Contributor III

Re: qlik sense formula to add numbers in the same field

Hi, I tried this using the below formula but it just showed me a string of the number with + in between

Replace([FREIGHT INVOICE VALUE])

result -> 

29+55+26.5+109

 So i did num(Replace([FREIGHT INVOICE VALUE], ' ','+'),'#,##0.##') instead, but got null as a result

Note: It would not accept the $ sign in the edited column/field 

$(

 

Luka
New Contributor III

Re: qlik sense formula to add numbers in the same field

I meant this for the first attempt
Replace([FREIGHT INVOICE VALUE], ' ','+')
Luka
New Contributor III

Re: qlik sense formula to add numbers in the same field

Hi Miskinmaz,

so basically I have a column containing different numbers and it varies.

the numbers are separated by space to indicate different amounts which used to be summed in excel

moving to QLIK, I can't mimic the excel formulas and a bit lost, don't think your method would work for me. My approach was to use the built in "split" mechanism in the table but it does not exist for this specific column i think due to data profiling by qlik

miskinmaz
Contributor II

Re: qlik sense formula to add numbers in the same field

Yes I agree if number varies then my solution won't work. But Tresesco solution should work for you. I guess u missed the $ and = sign
Luka
New Contributor III

Re: qlik sense formula to add numbers in the same field

So this is how i fixed it 🙂 

Thank you Treseco for the guidance !!

IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',1)),0,subfield([FREIGHT INVOICE VALUE],' ',1))+
IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',2)),0,subfield([FREIGHT INVOICE VALUE],' ',2))+
IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',3)),0,subfield([FREIGHT INVOICE VALUE],' ',3))