How to Lookup a value from another table and multiply it to another column? Involves a "SumProduct" of sorts

Anonymous — Wed, 30 Mar 2016 20:21:05 GMT

Hi,

I just started using qlik sense and I have two tables very similar to the following:

Quantity	Price	FX Rate	Currency	Product	Group
100	5	1.2	EUR	Product 1	A
75	4	1	USD	Product 2	A
60	3	1	USD	Product 3	B
500	1	0.95	USD	Product 4	C

And

Product	Volume
Product 1	600
Product 2	400
Product 3	100
Product 4	2000

Essentially, I want to show the proceeds of how much of the products I can sell in one day by group. The volume table shows the total volume traded in one day. Let us assume we can sell 20% of the volume. (Basically, if 20% of the volume is less than the quantity our formula would be .20 * Volume * Price * FX Rate. If 20% of the volume is greater than the quantity, then the formula would be Quantity*Price* FX Rate.)

I'm having issues with (a) referencing the volume in the table, and (b) having the formula at the line item level and aggregate by sum in the group level.

I want my output to be a table that looks like this:

Group	Proceeds
A	$900 ($600 from Product 1 and $300 from Product 2)
B	$60
C	$380

Can anyone please give me some advice on how to go about doing this? Thanks so much!

Re: How to Lookup a value from another table and multiply it to another column? Involves a "SumProduct" of sorts

Thu, 31 Mar 2016 07:59:33 GMT

When you load those two tables into Qlik, it will join them on the Product field - so you will end up with a single line containing everything you need.

your measure can then be:

SUM(IF(Quantity <= (Volume * 0.2),Quantity,Volume*0.2) * Price * [FX Rate])

with Group as your Dimension.

topic How to Lookup a value from another table and multiply it to another column? Involves a "SumProduct" of sorts in App Development

How to Lookup a value from another table and multiply it to another column? Involves a "SumProduct" of sorts

Re: How to Lookup a value from another table and multiply it to another column? Involves a "SumProduct" of sorts