Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jfkinspari
Contributor III

Re: Qlik sense sum of multiplied two fields

Perhaps Qlik has recognized the values as numbers but as text. That could explain, why they can't be multiplied. 

You could try making expressions that Sum the fields.

huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

Hello Jfkinspari, how to make it exactly?
i just tried by adding two measures again like Sum(PUR_Price), it returns numbers.
with Sum(PUR_Currency_1), it returns 0, so PUR_Currency_1 could be text instead of numbers ?
if yes, how to correct?
jfkinspari
Contributor III

Re: Qlik sense sum of multiplied two fields

Okay, I think you are closing in on the real issue.
PUR_Currency_1 is recognized as text, so Qlik can Sum it, and can't multiply using the field.
You need to convert the field, so Qlik think it's a number.
How you convert it depends on you are loading data into Qlik Sense using the Data Load Editor or Data Manager. Look for how to use the Num() function.
huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

thanks, however, Is it possible to change the format of PUR_Currency_1 in expression instead? otherwise i have to try when i can connect and loading with source data.
but during previous data loading, as there was dash behind numbers in certain fields of PUR_Currency_1, so i had did following during load:
Replace([PUR_Currency_1],'-','') as [PUR_Currency_1],
but didn't format the field into numbers.

huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

Reading some previous posts, it seems that EVALUATE() has to be used together with NUM.
Will try tomorrow...
LOAD
TEXT_NUMBER,
NUM(TEXT_NUMBER) AS NUMVLN,
EVALUATE(TEXT_NUMBER) AS EVLN ,
num( EVALUATE(TEXT)_NUMBER),'#0') AS EVLN2
huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

Hello Jfkinspari, as below pic indicated, i have tried with with various means of NUM(), Evaluate() etc, as red square circled expressions, but all failed, it gave the empty cells like red arrowed. what could be the reasons??

the original PUR_Currency_1 is like picture right side showed. 

kindly please let me know if you have the solution. thanks

11.jpg

jfkinspari
Contributor III

Re: Qlik sense sum of multiplied two fields

Use a combination of num# and num
num(Num#(Left(Cur,6),'#,##0.0000')) AS Cur2
where 'Cur' is your Currency field.
timpoismans
Valued Contributor

Re: Qlik sense sum of multiplied two fields

Just a tip, I'd keep using 

Replace([PUR_Currency_1],'-','')

to remove the '-' in case there are text values that differ in length.

 

 

 

 

huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

Hi Jfkinspari, Timpoismans, i tried in both ways, but all don't work out with value. kindly please suggest differently. thanks
timpoismans
Valued Contributor

Re: Qlik sense sum of multiplied two fields

What does it say if you just do the following:

Num#(Replace([PUR_Currency_1],'-',''))

 

Community Browser