You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Can anyone help me with the following SAP Business Intelligence problem. I have a bunch of orders that are split into tasks. I am trying to work out the duration of each task. I have dates for start and end of each task. The complexity is that the order can go ‘on hold’ - during an on hold period, the clock stops, so any on hold duration needs to be excluded from the task duration. Essentially I need to do the following for each task:
1) Work out the task duration: Task duration = Task close date – task start date
2) Identify if any of the periods that the order was on hold took place during the time the task was open (an order can go on and off hold multiple times)
3) Subtract any relevant (ones that took place whilst the task was open) on hold periods from the task duration to give me Task duration excluding on hold
Ideally I want to do this inside of SAP Webintellegnce, but could export the two tables into excel if anyone knows a way of doing it that way
My two tables look like this:
[img] 
[/img]
[img] 
[/img]
My head is getting sore from banging my head against my monitor…
That status change table is not much use to you unless it's still in the database. As a report in a form of a list, the excel work required to extract the data you need each time would be a ball ache. Can you not interrogate with a query that sums the periods between status changes?
Do you mean, make a table that showed the start and end date of each on hold period on a single row?
IF the status change table is generated (or can always be sorted) sorted by Order ID, then by Status Change date so that there is always an even number of rows for each Order ID, then you could put a conditional statement in the right hand column that always gives you the DATEDIF or YEARFRAC between each pair of OrderID rows, you can then use a conditional Sum for each OrderID to total the amount of Hold time in a given order
Hmm.... good idea. That would only give me the overall duration for all of the on holds for that order wouldn't it? I need each inidvidual one so i can then take away from each tasks duration.
So each order can have multiple tasks, you need the duration for each task, but the status changes are only linked to order ID and not task?
Yep. But if a order is on hold, all tasks are on hold.