Quantity based on Price Date Range

[nabilbrahimi]

Dear Qlik community,

In price table each Product/Production Plant the price vary.

In quantity table I have the quantity delivered with the delivery date.

I need to calculate for each "PRICE PERIOD" the quantity delivered.

Unfortunately with set analysis it doesn't works : sum( {<[Delivery Date]={'>=(=[Price Valid From])<=(=[Price Valid To])'} >} Quantity)

The best would be doing the calculation in script part and add the delivered Quantity to Price table, but I do not really know how doing it..

Price:

load * Inline [

Plant ,Product ,Price Valid From ,Price Valid To ,Price

Plant 1 ,Product A ,2017.07.04 ,2017.08.06 ,8 340

Plant 1 ,Product A ,2017.08.07 ,2017.08.15 ,8 500

Plant 1 ,Product A ,2017.08.16 ,2017.08.30 ,8 435

Plant 1 ,Product A ,2017.08.31 ,2017.10.16 ,8 610

];

Quantity:

load * Inline [

Plant ,Product ,Delivery Date ,Quantity

Plant 1 ,Product A ,2017.02.13 ,13

Plant 1 ,Product A ,2017.02.17 ,18

Plant 1 ,Product A ,2017.02.27 ,29

Plant 1 ,Product A ,2017.03.08 ,13

Plant 1 ,Product A ,2017.03.14 ,13

Plant 1 ,Product A ,2017.05.24 ,16

Plant 1 ,Product A ,2017.05.31 ,13

Plant 1 ,Product A ,2017.06.20 ,18

Plant 1 ,Product A ,2017.08.29 ,6

Plant 1 ,Product A ,2017.09.08 ,6

Plant 1 ,Product A ,2017.09.12 ,12

Plant 1 ,Product A ,2017.09.14 ,6

Plant 1 ,Product A ,2017.09.22 ,34

Plant 1 ,Product A ,2017.09.27 ,14

Plant 1 ,Product A ,2017.09.28 ,14

Plant 1 ,Product A ,2017.10.10 ,20

Plant 1 ,Product A ,2017.10.12 ,8

Plant 1 ,Product A ,2017.10.16 ,16

];

Thank you for!

[Anil_Babu_Samineni]

Actual condition should be this?

sum( {<[Delivery Date]={"=[Delivery Date] >= [Price Valid From] and [Delivery Date] <= [Price Valid To]"} >} Quantity)

OR

You can create flag like

If([Delivery Date] >= [Price Valid From] and [Delivery Date] <= [Price Valid To], 1,0) as Flag

And Analysis should be

Sum({<Flag = {1}>} Quantity)

[effinty2112 · Accepted solution]

Hi Nabil,

After loading your tables try:

Inner Join

IntervalMatch ([Delivery Date],Plant, Product) LOAD [Price Valid From],[Price Valid To],Plant, Product Resident Price;

this is the extended syntax version of InvervalMatch so it will be able to cope with different date ranges for Plant and Product.

Then for your data you can get this straight table:

Price Valid From	Price Valid To	Sum(Quantity)
		136
2017.08.16	2017.08.30	6
2017.08.31	2017.10.16	130

Kind regards

Andrew

[nabilbrahimi]

Thank you Andrew,

Works perfectly, this helped a lot.

Best regards,

Nabil