Calculate a new field via date range lookup

[samba]

Hello,

I'm rather new to Qlik Sense and currently building an analytics app for my purchasing department.
I have a large order table with lots of columns and one or more rows per order. Important for this case are the following fields, but there are more:

OrderID, Order Date, Contract (Y/N), Contract ID, Contract valid from, Contract valid to, Product, Location, Order amount (calculated), Quantity ordered, Price per item.

My task is to see how our contract orders are performing against our spot orders.

For each contract I have to lookup all spot orders with the same product and same location within the validity range of the specific contract. And of these spot orders I have to get the total quantity and calculate the weighted average price per item and compare it against our contract price per item.

I tried Intervalmatch but it did not work for me especially with all the conditions and is probably the wrong way?

Thanks for any input!

[Kushal_Chawda]

@samba  please share sample excel file with some data and expected output

[samba]

@Kushal_Chawda I think my phrasing is a bit confusing, I hope this helps understanding my "problem". (Edit: date format is DD.MM.YYYY)

[Kushal_Chawda]

@samba  How do you know that data with flag 'N' corresponds to contact id 1A or 1B because for those records contact id is NULL

[samba]

@Kushal_Chawda They do not correspond directly. For every 'Y' flagged row I want to use all 'N' flagged rows within the validity range of the 'Y' flagged row  (with the same location and product as additional conditions).

In other words: For every 'Y' flagged row lookup sum of order amount and sum of quantity ordered of all 'N' flagged rows with order date >= contract validity from and order date <= contract validity to and identical location and product.

It's also for me quite confusing to phrase it correctly, sorry.