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

[timpoismans]

Could you perhaps give us a sample app? 
You can't upload .qvf files, but perhaps you can upload an excel with sample data and a docx with measures. It's a very roundabout way, but I don't think the new community platform leaves us any other choice.

The sample app would make it easier for us to help you, as well as being faster instead of suggesting possible solutions again and again.

It seems extremely weird to me that it would just turn to 0 because of the changed format honestly.

[Vince_CH]

Okay， please find the source data i just exported from same app, with first 5 column are the original data extracted. kindly please let me know if you have any solution, thanks!

[jfkinspari]

In order to help you we really need the .qvf as the environment variables are important when setting the decimal and thousand separator characters

[timpoismans · Accepted solution]

@Vince_CH

Hey

I just uploaded the data from the Excel into Qlik Sense, without any transformations.

And then made this table:

As you can see, I have no issue performing the multiply and getting the comma as decimal seperator.

These are my predefined format variables (default in script):

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

The money variables aren't important, unless you want to display the values as currency.

[Vince_CH]

Hello Both, i will try as per suggstions, and let you know the results soon, thanks for the support always.

[Vince_CH]

Tim, it works now, super. i didn't change the thousand and decimal separator in the script, just use NUM() to format PUR_Price into same looking with dot decimal too. now they are looked same, also can be multiplied, thanks for solution, also thanks to Jfkinspari for the hint and directions in sort out the cause.

[timpoismans]

Glad your issue got solved.

For future reference, it's best to make sure your fields have the same format by applying the Num(), Date(),... functions in the script. In turn, you'll often avoid issues like this.

Please mark the appropiate answer as correct, or post the solution to mark as correct if you solved it yourself. Posts that helped you in coming up with the solution can be marked helpful.

Regards,

Tim P.

[Vince_CH]

Hello ,  for sure will mark up the appropriate one as accepted solution, but still regarding this same topic, i am trying to use KPI to show up the sum of multiplied value with currency in RMB only. the below expression is the formula for YTD value, but even i add into PUR_Currency={'RMB'}, there is no change in the numbers, why?

[Vince_CH]

Sorry, it was a mistake, i typed the field name wrong, so please forgot about above post