topic Re: Quantity based on Price Date Range in QlikView

Quantity based on Price Date Range

nabilbrahimi — Thu, 19 Oct 2017 08:58:42 GMT

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!

Re: Quantity based on Price Date Range

Anil_Babu_Samineni — Thu, 19 Oct 2017 09:56:50 GMT

Actual condition should be this?

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

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)

Re: Quantity based on Price Date Range

effinty2112 — Thu, 19 Oct 2017 10:12:54 GMT

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

Re: Quantity based on Price Date Range

nabilbrahimi — Fri, 27 Oct 2017 07:20:03 GMT

Thank you Andrew,

Works perfectly, this helped a lot.

Best regards,

Nabil