Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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)
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
Thank you Andrew,
Works perfectly, this helped a lot.
Best regards,
Nabil