Two Dates Issue

[Anonymous]

I have 2 Tables one containing PO#, Schedule Date and Schedule Qty and other table containing PO#, Delivery Date and Delivery Qty.

Now what I want is to show PO#, Schedule Date, Delivery Date, Schedule Qty and Delivery Qty in a Pivot Table.

at the m oment it is coming like this, which is wrong

now what i want it show is the schedule date and delivery date, with respect to its scheduled quantity and delivered quantity and there difference. The scenario is that the company defined a certain schedule dates and there quantities. The issues are

1. Now vendor is delivering products some times on the given scheduled date and some times delayed and we need to know the delay.

2. Vendor deliver quantity which some  times match the scheduled quantity, some times below it and sometimes greater than it, what we need to do is to match the delivered quantity with the scheduled quantity, if its matching than the difference shows 0, if its bellowed it than it shows the difference or left quantity and the scheduled quantity would be completed in next delivery made by vendor. If its above the scheduled quantity that it shows the overflowed quantity in difference field and it would be  filled in next scheduled quantity.

it would be showed like this

PO#	Schedule Date	Delivery Date	Schedule Qty	Delivery Qty	Difference
1	1/1/11	1/1/11	20	10	10
1		2/1/11		10	0
1	2/1/11	3/1/11	50	50	50
1	3/1/11	4/1/11	100	200	-100
1	4/1/11	4/1/11	150	100	50
1		5/1/11		100	-50
1	5/1/11	5/1/11	100	50	50
1		6/1/11		50	0
1	6/1/11	7/1/11	200	50	150
1		7/1/11		50	100
		8/1/11		50	50
		9/1/11		50	0

attach also please find the qvw i am working on. Please any help would be highly appreciated. Thanks

[swuehl]

Could you please attach the qvw? I am not able to see your attachment.

I noticed that all records show same PO#. Is this what you expect? Since PO# will link the two tables, you now link each record of Delivery table with each records of schedule table.

[Anonymous]

Sorry i didnt attached the sample qvw. now you could view the attached qvw file for more details.

for one PO#, there are multiple schedule dates, and mulitple delivery dates. Schedule dates, Sscheulde Quantity and PO# are coming from one table and Delivery Dates, Delivered Quantity and same PO# coming from another table. now you could match the PO# but for one PO# there is no link exist between the schedule date and delivery date. Because if we go to basic Database modeling than there is always one table which is known as Schedule Detail table. which shows that for one Schedule# there is one schedule date, schedule quantity, Delivery dates and Delivered Quantity. But here it didnt exists. So this issue rise. Thanks.

[syed_muzammil]

Hi,

Please check if that is what you want

[swuehl]

Ahmed,

without a link between a specific schedule date with its quantity and the subsequent deliveries, I can't think of a method to derive that table that you required in your original post (without making a lot of assumptions). For example,

if you get a delivery date of 5/1/11, how do you know that it is the delivery related to schedule 4/1/11 and not to schedule 5/1/11? Or a much more delayed delivery to 3/1/11 or 1/1/11? If you get two deliveries on one day, how do you decide which delivery is related to which schedule?

In your above requested table, you want to analize the data per schedule, but there is no link, as you said, so your problem is underdetermined. You need to add this link, the schedule# , from the original basic database you mentioned, if you want to analize per schedule.

If you want to analize per PO number, you could easily aggregate the quantities however, like in attached sample.

All the best,

Stefan

[Anonymous]

Hi,

No, Thats not what i want. if you show these tables seperatly than that would not show the delay analyzation which my client want. what he want is to show in a pivot table the po#, its schedule date and quantity and the delivery date and quantity which will full fill the schedule quantity. and th issue is there is no link between these dates, what they are doing in SAP is a FIFO method, the first delivery quantity would full fill the first schedule quantity, and than the next delivery would full fill the next schedule quantity, only if the previous quantity is finished, otherwise the deliivery would go into the previous one.

[Anonymous]

Hi,

No your attached sample didn't help me in this scenario, hmm Yes, you are right, that's what i was thinking that its like way above the difficult level to make assumptions and on those assumptions we have to run a BI, well may be this could be done by some tough programming, but programming is not a what BI do, am I right?
Well what knowledge i get from the current scenarios is that that they are using a some FIFO method in SAP in which the first delivery would come and it will automatically cut the first scheduled quantity,

if, the quantity is finished than the next delivery would cut the next schedule quantity,

else, the the next delivery will finish the first schedule quanityt than go to next.

But the issue is they dont have any table for it and no link, so that why I need help from you guys, that may be there could be some programming in Qlikview or something else which I don't know.

[swuehl]

Well maybe one can program some script that may come close to what you want.

What you are saying sounds to me that deliveries are not triggered by schedules, so a customer purchases some stuff from time to time, but you also deliver goods to the customer, but don't track any linkage, so it's more or less by accident, that the customer got all the goods he purchased. But we are lucky, since the customer keeps purchasing in we keep delivering, the expected amount of good for a specific purchase is being delivered some point in time, even we the customer is already awaiting the next load.

As far as I know ERP systems and SAP, I can't really believe that this is planned to work like that. So I would strongly suggest that you try to get down to the data that gives us the link between schedules and deliveries (and how I understood one of your earlier postings, there is one ... Schedule Detail table )

[vijay_iitkgp]

Hi Ahmed,

Please find attached Script and sample excel sheet and run this script. Hopefully it will help you.

Regards

Vijay

[vijay_iitkgp]

The report would look like this.