I have a scenario, where I have some customers with their statuses viz Active, Passive Withdrawal, On Hold, Terminated, Completed. I also have a date column as status_date which has all the dates on which the status gets changed for each customer.
My requirements are:
How many days my customers have spent at each status from Active till Completed.
Average number of days spent by customers at each status. For example, if 3 customers spent 2,5,8 days respectively on ‘On-Hold’ Status, the On-Hold Average days would be (2+5+8)/3 = 5.
Since, I only have a single date column, I’m using a lag function to calculate a previous date in other column and then making the use of Datediff function to get the days difference between each status change.
Issue I’m having there is, the Lag function doesn’t take +1/-1…it just takes the whole number and displays one previous value against the latest. So when I’m calculating the date difference, the column gives me the gaps days for the previous status instead of the latest status. Hence, I’m not able to map the statuses against the days spent correctly.
I’ve attached a sample file to understand the data.
Logic I’m using for Previous date:
lag(min({status_date}), [{status_date} ASC],1,[{completed_customers}])
Days Spent: dateDiff({Previous Date}, min({Status_date}), ‘DD’)
I was wondering if I can use the lag function to get the next date ahead instead, so that I can calculate the difference correctly against the statuses. Or if there’s any better way to handle these scenarios.
Issue I’m having there is, the Lag function doesn’t take +1/-1…it just takes the whole number and displays one previous value against the latest. So when I’m calculating the date difference, the column gives me the gaps days for the previous status instead of the latest status. Hence, I’m not able to map the statuses against the days spent correctly.
you want to calc the diff between these?
Maybe you can share the small sample data or a Arena dashboard. That we can play around.
Thanks a lot for responding. And I ideally need to have the number of days my customer spent on each status throughout its journey. So, if I have status changed for him from previous, I need to have the difference of days there…so that I can see how many days he spent on the last one.
And subsequently I have to calculate the average number of days spent on each status by customers.
And sure…let me share this data on Arena.
Hi @HimaniS
do you have a more detailed date (on hours). Because your current case is created that it will work but if “Active” and “On-Hold” are on the same day you cant order right.
Yes…I know about these scenarios when Active and On-Hold and some more of status changes happening on the same day and we do have a detailed date for each, but that is okay. As in scenarios like these, for my previous status the days spent would be 0, which is fine.
Coming to the other question, so I want two things from this data:
How much time is spent on each status till they complete? For example, based on CustA , he spent 0 days while he was Active first and then changed to On-Hold the same day, after that he changed to Active again on Nov 7,2023, which means he spent 8 days on ‘On-Hold’. Then again, the status got changed the same day and he was completed, which means he spent 0 days on ‘Active’ again. Final outcome what I get is:
Active: 0 days, On-Hold: 8 days, Active: 0 days
Average Time spent on each Status by all customers? So, once I have all the data for the days spent on each status, I can take the cumulative sum for each.
And then I’ll check how many customers were there on each status, suppose 80 customers had ‘On-Hold’ status, I’ll check for the Total days spent on On-Hold and divide it by 80 to get the Average days.
Because there has not been activity in this thread this topic will be archived. @HimaniS if you still need help with this specific question, please feel free to repost your question at the top of the community to make it a priority for the community experts.
If you were able to find a resolution please feel free to post it here to help the community!
