Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nabilbrahimi
Contributor II
Contributor II

Quantity based on Price Date Range

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!

1 Solution

Accepted Solutions
effinty2112
Master
Master

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.162017.08.306
2017.08.312017.10.16130

Kind regards

Andrew

View solution in original post

3 Replies
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)



Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
effinty2112
Master
Master

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.162017.08.306
2017.08.312017.10.16130

Kind regards

Andrew

nabilbrahimi
Contributor II
Contributor II
Author

Thank you Andrew,

Works perfectly, this helped a lot.

Best regards,

Nabil