hi! I need help to calculate an indicator related to the purchasing requisitions backlog at every month end. We receive purchasing requisitions that we need to convert in purchasing orders in SAP. The objective is to calculate how much requisitions are pending at end of month and track the evolution of the indicator.
Today I calculate the status of each requisition based on two columns: requisition creating date and purchase order date. If the purchase order date has no date, requisition status is “Open”. If there is a date, requisition status is “closed”.
I can track today the end of month picture, but I cannot calculate the previous months indicator.
The data model is pretty basic:
Requisition Number - Requisition date - Purchase Order number - Purchase Order date
Can you please draft your desired output as well based on this?
Because the data only shows month 5 but in your first post you mention wanting to calculate the previous month indicator so I assume your actual dataset covers the current month as well? This matters for the proper calculation method.
Requisition Number - Requisition date - Purchase Order number - Purchase Order date
This is not reflected by the file you uploaded correct? So the records that you uploaded reflect either of the two?
You mention wanting to calculate the pending records, but that’s different than filtering on Status = Open?
Anyway, please help us helping you by providing more details about your input, logic that should be applied and desired output. The more you can provide, the better the help will be
@ArjenEX I am attaching a new file with more data.
Yes, I received purchasing requisitions every month. I hope the new file provides better perspective.
I simplified the model in my original request. The file I use is the one attached.
Yes, it is different. I.e: if a requisition does not have a PO by 31/5, it will be shown as open by 31/5. If a PO is created by 2/6, when I run the report by 30/6 it will be shown as “closed”, which is okay, but I cannot compare the backlog by 31/5 and 30/6.
If I had 30 requisitions as “open” by 31/5 and this requisitions are closed by 2/6, when I try to recalculate the backlog by 31/5 the backlog will be shown at cero. I need to somehow store or calculate wh PR file v2.xlsx (3.5 MB)
ich was the backlog by 31/5 in the future (to compare purchasing order creation date vs. the end of month date could be an option but I don’t know how to do it).
Try this. Select year and month of interest. It provides a monthly summary, year to date summary and current status of individual requisitions. I haven’t QAed it very carefully. If you find bugs let me know.
