Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I have a list of orders, the status they changed into and the date of the stauts change. I want to calculate the days that each order stayed in each status.
In other words the last column in the table below (the one with the red header text).
Is it better to do it in load time or in a straigh table for example using set analysis?
Thank you all in advance.
Order Id | Date | Status Description | # of Days is Status |
1 | 15/7/2013 | Approved | 1 |
1 | 16/7/2013 | Awaiting | 9 |
1 | 25/7/2013 | Processing | 0 |
1 | 25/7/2013 | Finished | 0 |
2 | 15/7/2013 | Approved | 6 |
2 | 21/7/2013 | Awaiting | 4 |
2 | 25/7/2013 | Processing | 0 |
2 | 25/7/2013 | Finished | 0 |
3 | 16/7/2013 | Approved | 5 |
3 | 21/7/2013 | Awaiting | 4 |
3 | 25/7/2013 | Processing | 2 |
3 | 27/7/2013 | Finished | 0 |
4 | 10/7/2013 | Approved | 0 |
4 | 10/7/2013 | Awaiting | 15 |
4 | 25/7/2013 | Processing | 1 |
4 | 26/7/2013 | Finished | 0 |
5 | 25/6/2013 | Approved | 15 |
5 | 10/7/2013 | Awaiting | 7 |
5 | 17/7/2013 | Processing | 4 |
5 | 21/7/2013 | Finished | 0 |
See attached example.
I had a similar use case and found that doing it in the script made sense for me for performance and flexibility reasons.
Performance - I was dealing with about 200 million records, so doing the calculations up front vs. doing calculations on the fly was the better route
Flexibility - Was able to use results in multiple objects in my dashboards without repeating expressions. Also was able to create buckets of days ex. 1-5 days, 6-10, etc. using Class() . So you can ask the question: How many orders are waiting 6-10 days to be processed.