Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
huichunchang
Contributor

Qlik sense sum of multiplied two fields

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!

11.jpg

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

Accepted Solutions
timpoismans
Valued Contributor

Re: Qlik sense sum of multiplied two fields

@huichunchang

 

Hey

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

And then made this table:

Multiply_Solve.PNG

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.

38 Replies
jfkinspari
Contributor III

Re: Qlik sense sum of multiplied two fields

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?
huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

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
Contributor III

Re: Qlik sense sum of multiplied two fields

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))
huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

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
Valued Contributor

Re: Qlik sense sum of multiplied two fields

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.

huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

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
Contributor III

Re: Qlik sense sum of multiplied two fields

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.
huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

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 "-"
huichunchang
Contributor

Re: Qlik sense sum of multiplied two fields

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?!?

11.jpg

Community Browser