Solved: Re: Qlik sense sum of multiplied two fields - Qlik Community

Vince_CH · ‎2018-12-03

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 · ‎2018-12-05

@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.

View solution in original post

jfkinspari · ‎2018-12-03

The correct calculation will depend on the granularity of PUR_Price and PUR_Currency_1.

The two columns with PUR_Price and PUR_Currency_1 are they simple dimensions or expressions in the table shown?

Vince_CH · ‎2018-12-03

Hi Jfkinspari, PUR_Price is the different value depending the materials. PUR_Currency_1 is the exhcange rate depending on the currency at a few selections.

jfkinspari · ‎2018-12-03

Okay, so if we just assume only one unique value of both PUR_Price*PUR_Currency_1 is available per row, you should just be able to write the formula as : SUM(PUR_Price*PUR_Currency_1) or to be more precise: Sum(Only(PUR_Price)*only(PUR_Currency_1))

Vince_CH · ‎2018-12-03

Hello, it might be not unique value, since it is for purchase data over the years, so it might be same mateiral be purchased at same price and exchange rate, however, the PUR_Order_Number, PUR_Order_Item, PUR_Material_Number are the 3 factors to decides its uniqueness of this row.

timpoismans · ‎2018-12-03

The measure that @jfkinspari suggested should still work.

On top of that, having 3 values decide on the uniqueness makes it less likely there will be double values.

Please try

SUM(Only(PUR_Price)*Only(PUR_Currency_1))

Regards,

Tim P.

Vince_CH · ‎2018-12-03

Hi Jfkinspari, with your first expression, it returns 0, with second one, it returns with "-". Aggr() has to be used in this case, but i am not sure how to put it correctly to achieve the results

jfkinspari · ‎2018-12-03

To debug I would suggest you create two new columns each displaying
Only(PUR_Price)
Only(PUR_Currency_1)
At least one of those two expression must return "-" for a row in order to explain the behaviour.

Vince_CH · ‎2018-12-03

No, it dosn't work, with SUM(Only(PUR_Price)*Only(PUR_Currency_1)) it indicated wrong expression, if by Only(PUR_Price)*Only(PUR_Currency_1) for last column, it returns "-"

Vince_CH · ‎2018-12-03

Hello, it seems strange, with both only(), it works will as below illustrated, same as well with Agg(), but both failed in multipling these two fields. what is the cause?!?