Aggregating data with apply map

Mon, 26 Jan 2015 18:04:04 GMT

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?

topic Aggregating data with apply map in QlikView

Aggregating data with apply map