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)),'#.##')

[Vince_CH]

It returns empty cells.

[Vince_CH]

Hi All,
having tried, it seems following two returns with values, but in result like 76515 instead, what i expect is format like 7,6515 (comma is the decimal separator), how to correct?

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

[jfkinspari]

If case below doesn't work, it would perhaps be easier if you uploaded a sample app 🙂

Try

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

[Vince_CH]

Dear ,

i used following to make it now, but the looking of decimal sepator are different compared with orignial version, one is dot, another is comma, seeing below, how to make them uniform?

Num(Num#([PUR_Currency_1], '#,####-', '.', ',')/10000,'#,####') AS [PUR_Currency_1],

[Vince_CH]

Hi Jfkinspari, not sure if you know that qvf file is not be able to uploaded so far.
your formu works also, just the decimal separator is same like mine, in ".", different from looking of original ",".

also just i am confused about the meaning of ' , ' , ' . ' inside the expression?

[jfkinspari]

No I wasn't aware of any problems uploading .qvf files, but a solution is to zip the file before uploading

You can also look into the num#() and num() to understand how it interprets and format numbers in regards to comma and thousand seperator

[timpoismans]

That solution won't work either. Or it didn't work for me yesterday at least.

@Vince_CH
The difference between num#() and num() is the following:

Num# is used to change, if possible, a string to a number. The input is like (yournumberastext,'format of how you want your number','decimal seperator','thousand seperator')

Num works the same way as Num#, but takes a number as input, not a string.

The only field you need to have though, is:

For Num# you need a number as a string

For Num you need a number.

If you only use those parameters in the function, the format and seperators will go to default and should match the values showing in your application.

Regards,

Tim P.

[Vince_CH]

Hi Tim, thanks for explaining in words, however, as i tried with the expression from Jfkinspari like: Num(Num#(Replace([PUR_Currency_1],'-',''),'#,####',',','.')) AS [PUR_Currency_1]
the decimal separator indicated actually is comma in ',' but also loading it appears to be '.' again in decimal position, ??

[timpoismans]

What does it give if you try the following:

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

This should default to the format of your app (the one that's set on the very first page of the script, all the variables being SET).

[Vince_CH]

Hi Tim, it works, now the appearance look same, but the multiplied two fileds (PUR_Price*PUR_Currency_1) become zero.