Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
your measure can then be:
SUM(IF(Quantity <= (Volume * 0.2),Quantity,Volume*0.2) * Price * [FX Rate])
with Group as your Dimension.