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]

[#2]

This script looks like it has worked. I was able to substitute in the actual data and am getting a valid number. It would be great to be able to fix the script so I did not have to add each month every time but maybe that is not an option?

[swuehl]

And how did you do the monthly calculation by hand?

[sunny_talwar]

I guess it might be helpful to know how you calculate average:

Average – Which average?

[#5]

I use excel and a pivot chart. This gives me the total interactions by MSL per month. I then add these numbers together to get a total and then divide this total by the number of months included.

[#6]

Based on this article, I calculate the average both ways. For the current KPI I want the year to date average so I this way listed in the article makes sense: Avg( Aggr( Sum(Amount), MonthID ) )

However, there are times when I just want the average number for one month. This would need to be a different KPI or visualization and not what I need for this particular problem.

[swuehl]

Ryan McClellan wrote:

I use excel and a pivot chart. This gives me the total interactions by MSL per month. I then add these numbers together to get a total and then divide this total by the number of months included.

I think that's what my table shows (using your inner aggregation Count() )

[#8]

I found something interesting with this script... If I remove the other months and just keep Aug. I only get a total count of interactions for Aug rather than the average for Aug. The total count is correct but strange it does not give the average.

This makes me wonder if the script is working correctly...

[swuehl]

I think I am lost with regard to what you are trying to explain.

Maybe another day...

Guess Sunny is already preparing a demo QVF

[sunny_talwar]

I was hoping you will handle this Stefan. I am not sure what is needed and how can it be calculated

UPDATE: Don't give you on Ryan now

[#11]

I appreciate all the help. Let me know what I need to clarify to help.

I will try again with what I need. I need to know the monthly average of interactions per liaison for a product based on 'Liaison Type.' I would like to be able to show this on a per month basis.

I then would like a year to date monthly average of interactions per liaison for a product.

I can have separate KPIs per product and per liaison type.

Hopefully this helps?