September 5, 2016 at 5:01 am #5855
I have a data dump from Oracle that shows the process of a Financial approval process, the ‘Action Date’ is all in one column. Sometimes there could be a number of different actions until it is approved. At the moment I can find the overall lead time of the process but how can I get the time between individual steps if the dates are all in the same column? Each process has a Unique ref number and a process sequence number. Any help would be greatly appreciated.September 5, 2016 at 6:43 am #5857
One way to proceed is with a couple of calculated columns to capture dates for previous and next sequence step that belong to the same process. Often done with Functions like EARLIER, FILTER and TOPN. I did not notice a column in the image that would identify the specific process instance.
Can you attach a workbook with anonymized data, listing a couple of processes and their sequence numbers?
TomSeptember 5, 2016 at 7:39 am #5858
Attached is a cut from my data, there are two separate processes with a unique ID. If you have anymore questions please let me know.
ThanksSeptember 5, 2016 at 10:24 pm #5867
Attached workbook used calculated columns to figure out Lead and Lag days for each task.
Lead days = days between previous action date to “current” row action date.
Lag days = days between “current” row action date and next action date.September 6, 2016 at 7:35 am #5877
Thanks for the help.
SamSeptember 6, 2016 at 4:00 pm #5879
You are welcome!
Please also tell a friend about the PowerPivotPro forums!
TomSeptember 7, 2016 at 10:28 am #5895
The calculations work when transferred into my data model, the only issue is when I try to refresh the linked table I get an error around a circular dependency? Is there a way around this?
I know in powerpivot you can have issues running calculations on calculated columns. I have attached a screen shot of the error.
SamSeptember 7, 2016 at 3:27 pm #5899
Are you using Excel 2010, 2013 or 2016?
TomSeptember 8, 2016 at 2:55 am #5919
Have not heard back from you. Were you able to resolve the issue? If not, your version will make a difference in my answer.
TomSeptember 8, 2016 at 4:13 am #5920
My email failed to send for some reason last night. I am using excel 2016.
SamSeptember 8, 2016 at 9:35 pm #5941
Sometimes that error message arises with calculated columns when the Power Pivot formula engine does not see a unique row identifier in a single column.
I have replaced all calculated columns with measures after importing the data through Power Query (Get & Transform group on the Data tab of the Excel ribbon). In Power Query I created a unique row identifier ([Action ID]) in a single column, which is a concatenation of Org Code, Req No and Apr Seq No. In addition, I added another column (Request ID) that is a concatenation of Org Code and Req No.
Power Pivot in Excel 2016 allows for use of variables, which makes writing many complex formulas less of a chore.
Please feel free to ask any questions regarding how the measures were put together.
One important item: because of the way I have implemented totals for Lag and Lead, it will be important that a column like [Action ID], a unique row identifier (primary key) is built into the table. The column does not have to have the same format (a concatenated string), but it needs to be one column with a unique value in every row.
TomSeptember 14, 2016 at 10:46 am #6077
Apologies for the late reply! Thanks for the help, really appreciate it. I have the model working perfectly now and the linked table refreshes with no issues.
SamSeptember 14, 2016 at 1:55 pm #6083
Thanks for following up. Glad that there are no more issues when refreshing.
If there is an opportunity to do so, please tell a friend about the PowerPivotPro forums!
Looking forward to working with you again,
You must be logged in to reply to this topic.