1

Scenario

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.

enter image description here

Goal

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.

SAQL

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

date_diff("minute",toDate(CreatedDate_sec_epoch),toDate('OrderId.CreatedDate_sec_epoch'))

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.

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.