topic Re: Calculate a new field via date range lookup in App Development

Calculate a new field via date range lookup

samba — Thu, 29 Aug 2024 14:33:28 GMT

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!

Re: Calculate a new field via date range lookup

Kushal_Chawda — Thu, 29 Aug 2024 15:27:37 GMT

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

Re: Calculate a new field via date range lookup

samba — Sat, 31 Aug 2024 10:53:20 GMT

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

Re: Calculate a new field via date range lookup

Kushal_Chawda — Mon, 02 Sep 2024 10:20:50 GMT

@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

Re: Calculate a new field via date range lookup

samba — Mon, 02 Sep 2024 10:33:08 GMT

@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.