How to get KPI of average calls per person per month

[#1]

Hello,

I am trying to make a KPI object that shows average number of calls per person per month. I currently have the following script but can not get the per month part of the calculation. Any assistance is appreciated.

Count({$<[Liason Type]={'MSL'},[Product: Description]={'Apples'}>} DISTINCT [Call: Call ID])/count [Datetime.autoCalendar.Month]

[swuehl]

Ok, this is what I get when filtering on August 2016:

What do you get (remember, images may be more descriptive than just words)?

[#3]

Ok, I get the same.

[swuehl]

Ok, and when looking at these single rows for August 2016, how exactely do you want to derive your numbers from that?

[#5]

I think the issue is the following and I understand how the script is calculating the values: The Aug value is 50.67. This is derived from taking the total number of interactions for Aug (152) and dividing by the number of Liaison types (which is 3 in Aug). This is not what I need.

I want to take the total number of interactions in Aug (152) and divide by the total number of liaisons that have a 'Liaison' type of MSL (there are 11 MSLs that should be associated with Aug and Oranges). So in this case, 152/11. This would give a value of 13.8.

Make sense?

[swuehl]

What is the expression you are using to get your number 11?

[#7]

Not using an expression. Just counting the number of liaisons in the data the have a 'Field Team' value of CNS. These liaisons are associated with the Product 'Oranges' In the QVF there is a Field Team Roster with this information.

[sunny_talwar]

If you can take your csv file and delete all the extra rows of data which we are filtering out using set analysis or selections and show how you got to 13.8, we might just be able to get this done.

[swuehl]

That's valuable information (and quite new to me).

This will reproduce your number for August:

Sum(

Aggr(Count({$<[Liason Type]={'MSL','RAD'},[Product: Description]={'Oranges'},[Datetime.autoCalendar.YearMonth]={'2016-Apr','2016-May','2016-Jun','2016-Jul','2016-Aug','2016-Sep'}>} DISTINCT [Call: Call ID])

,[Datetime.autoCalendar.YearMonth], [Liaison Type-Liason Type]))

/

Count({$<[Field Team]={'CNS'},[Product: Description]={'Oranges'},[Datetime.autoCalendar.YearMonth]={'2016-Apr','2016-May','2016-Jun','2016-Jul','2016-Aug','2016-Sep'}>}

[Field Team])

[#10]

This did not work. It actually increased my Aug value. It also changed the Aug MSL total from 129 to 12.9. I can not seem to find the reason why?

[#11]

The CSV does not have the Field Roster data set that is found in the QVF. This contains the number of MSLs associated with the 'Oranges' product as these MSLs are also associated with the 'CNS' field team. This is where the value of 11 is coming in to play as there are 11 MSLs associated with the 'Oranges' product. Thus 152/11 = 13.8