Qlik sense sum of multiplied two fields

[Vince_CH]

Hello Community, 

as below,  i intend to multiply PUR_Price*PUR_Currency_1 and sum finally at KPI, however, the last column resulted in zero, and i looked at earlier posts in qlikview and also tried with following expressions, but all didn't work out on my qliksense, anyone can help ?? thanks!

• PUR_Price*PUR_Currency_1
• num(Sum(Aggr(PUR_Price,PUR_Order_Number,PUR_Order_Item,PUR_Material_Number)*Aggr(PUR_Currency_1,PUR_Order_Number,PUR_Order_Item,PUR_Material_Number)),'#.##')

• num(Sum(Aggr(Sum(PUR_Price)*Sum(PUR_Currency_1),PUR_Order_Number,PUR_Order_Item,PUR_Material_Number)),'#.##')

• num(Sum(Aggr((PUR_Price*PUR_Currency_1),PUR_Order_Number,PUR_Order_Item,PUR_Material_Number)),'#.##')

• Sum(num(Aggr(PUR_Price,PUR_Order_Number,PUR_Order_Item,PUR_Material_Number),'#,##')*num(Aggr(PUR_Currency_1,PUR_Order_Number,PUR_Order_Item,PUR_Material_Number),'#.##'))

• num(Sum(Aggr(PUR_Price,PUR_Order_Number,PUR_Order_Item,PUR_Material_Number)*Aggr(PUR_Currency_1,PUR_Order_Number,PUR_Order_Item,PUR_Material_Number)),'#.##')

[jfkinspari]

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.

[Vince_CH]

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]

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.

[Vince_CH]

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.

[Vince_CH]

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

[Vince_CH]

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

[jfkinspari]

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

[timpoismans]

Just a tip, I'd keep using 

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

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

[Vince_CH]

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

[timpoismans]

What does it say if you just do the following:

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