We keep orders information into Salesforce and we have our own statuses too, whenever the order status changes in our Ecom system it updates that into Salesforce. I can get the timings via Order History object, there might be case where the status can go through many stages. Following example only show status change between In CHQ to Complete but as I said there might be other statuses too in between like In CHQ -> Pending -> Review -> Complete, those will be tracked here too.
I want to calculate the difference between them and want to show on Einstein Analytics dashboard, it can be any chart and if we consider multiple orders with different statuses then of course I need to calculate average time.
I'm having hard time figuring out how I can calculate time difference between multiple rows using SAQL, so for now what I've done is I've calculated the difference between Order Created date and Status Change date.
q = load "Order_History"; q = filter q by 'Field' == "Status"; q = group q by ('OldValue', 'NewValue'); q = foreach q generate 'OldValue' as 'OldValue', 'NewValue' as 'NewValue', count() as 'count', -avg('time_diff')/60 as 'avg_time_diff'; q = order q by ('OldValue' asc, 'NewValue' asc, 'avg_time_diff' desc); q = limit q 2000;
time_diff is calculated by computeExpression in dataflow and it's in seconds
And the outcome is this with my test data, but this is calculated against orders created date not with previous status which is what I wanted to do. The bar chart is grouped by Old Value > New Value and it shows what was Old value and then new value and what is the count and then the average time_diff.
I also want to know is it possible to make chain of this statuses? Like it will show the whole Order flow with time difference, this only sounds cool but I can't even come up with single chart where this can be possible.
Any help is appreciated.