I am loading data from an excel file. I have a tab in the spreadsheet called "Events" , which includes the field names ServiceLine, CaseID, EventName and EventDate. I am focusing on one particular service line, but within that service line there are several case id's. Each case id has multiple EventNames and EventDates associated with it. My initial goal was to group the EventNames into smaller Stages, Get the Max EventDate for each Stage and then Create a field to calculate the number of days spent in each case.
I created a map on the EventName which groups the data into Stages.
Map1:
Mapping Load
* INLINE [
EventName,Stage
Initiate Call,Stage1
Complete Demographics,Stage1
Assign MA,Stage1
Intake Complete,Stage1
FBDC Accepted,Stage2
FBD Search Complete,Stage2
Report Delivery,Stage3
Client Reporting Date,Stage3
Soft Close Date,Stage4
Case Closed,Stage4];
load
EventName,
ApplyMap('Map1', EventName, 'Not FBD') as Stage
I was able to calculate the Max EventDate in a straight table using the expression
aggr(max(EventDate),Stage). I refer to this as Stage Date
I was also able to get the number of days between each stage by using StageDate - above(total(StageDate))
The only issue is that it doesn't work when I have more than one case selected. How do I go about getting this information for each case? My end goal is to have a funnel chart which shows the average number of days spent in each stage for all of the cases within the serviceline. My thinking is that I have to calculate the number of days at the case level first, sum that number and obtain the average. Am I even going in the right direction?
