How to Lookup a value from another table and multiply it to another column? Involves a "SumProduct" of sorts
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!
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.
