I have a dataset where a single order will be submitted for inspection multiple times. The order number is alphanumeric string, and each submission for inspection has a sequence number. I created a calculated field to give me [Unique Order Inspection ID] = ([Order]+[Inspection Sequence #]). The date an order is submitted for inspection is different from the date the order is actually inspected (there's a queuing process).
I need to know the number of days from an inspection to the next time it is submitted for inspection.
I created a calculated field for the [Previous Unique Order Inspection ID] = ([Order]+STR([Inspection Sequence #]-1)).
Now how can I get the Previous Unique Order Inspection's [Inspected Date], so that I can calculate the difference from the previous [Inspected Date] to the [Submitted Date]?
For example, [Unique Order Inspection ID] 12-345678A2 [Submitted Date] 2/3/2019 - [Previous Unique Order Inspection ID] 12-345678A1 [Inspected Date] 1/5/2019 = 29 [Days from Previous Inspection to Submission]
I tried using the CASE formula, but it doesn't seem to be the appropriate formula. I feel like a LookUp of some kind should work to Find the [Unique Order Inspection ID] that matches the [Previous Unique Order Inspection ID] and Return the [Inspected Date]. I'm hoping to do this as a calculated field (rather than a window calculation) so that I can use the value in this calculated field in other calculations.
By knowing the number of days between inspection and subsequent submission, I can analyze across thousands of orders (not in this data sample) if there's a trend by the [Submitted By] field, if certain people resubmit their orders for inspection faster than others. I can also filter out the [Days from Previous Inspection to Submission] of any submissions after the [First Passed Date Per Order]. Perhaps the goal is to have all orders pass an inspection within 60 days, I could see who in the Submitted By field is meeting that goal across their orders. If someone (Bob) isn't meeting that goal, it could be because of the long times between submissions prior to the first passed inspection. But I may not care that Jane has long times between submissions after the first inspection has been passed, and so I will filter out those [Days from Previous Inspection to Submission] values if the Inspected Date is after the [First Passed Date Per Order].
Please let me know what the right formula would be to return a dimension (in this case [Inspected Date]) from a specified string entry (in this case [Previous Unique Order Inspection ID]).
Sample Order Inspection.twbx 15.4 KB