1 Reply Latest reply: Mar 31, 2016 3:59 AM by Andrew Mein RSS

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

    Connor Hogendorn

      Hi,

       

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

      QuantityPriceFX RateCurrencyProductGroup
      10051.2EURProduct 1A
      7541USDProduct 2A
      6031USDProduct 3B
      50010.95USDProduct 4C

       

      And

       

      ProductVolume
      Product 1600
      Product 2400
      Product 3100
      Product 42000

       

      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!