How to Lookup a value from another table and multiply it to another column? Involves a "SumProduct" of sorts
I just started using qlik sense and I have two tables very similar to the following:
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:
$900 ($600 from Product 1 and $300 from Product 2)
Can anyone please give me some advice on how to go about doing this? Thanks so much!