Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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:

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!

1 Reply
Not applicable
Author

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.