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]

OK, I believe this is working but it is still adding the average from each month together and the KPI shows this value. If I divide the expression by 12 then I get the value I need. However, I need the KPI to calculate the average at any given time in the year. Hard coding the number of months is not practical.

[sunny_talwar]

Count(DISTINCT MonthYear) isn't working?

[#4]

I know have this script. It gives the cumulative average for each month by not per month.

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

[swuehl]

Maybe you need to restrict the inner aggregation also to the year of interest, either by adding a set modifier to inner set analysis or maybe using a Year-Month field as aggr() dimension.

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

Set Analysis in the Aggr function

If this doesn't help, then maybe I don't really get what your issue is. Could you post some screenshots of your current setting under different selections scenarios or how you derive that is aggregating incorrectly?

[#6]

Thanks but this only lowered the total. How I know this is not working correctly is that the KPI total I receive with the above script is 189.7. What I would like to see is that value divide by the number of months to date. For example, if all of 2016 was done and the interaction totals stayed the same, then the average would be 189.7/12 for a value of 15.8 interactions per liaison type per month.

If 2016 was only five months over and the interactions to date were 189.7 then I would want to take 189.7/5 for a value of 37.9 per liaison type per month.

Does this make sense?

[swuehl]

Are your calls IDs maybe linked to multiple months? Since you are doing a distinct count, the total for a year might be close to the month count.

[swuehl]

This is what I get using the data you posted to the other thread:

The average total does not seem to be totally wrong:

Monthname	Avg(Aggr(Count({$<[Liaison Type]={'MSL'},[Product: Description]={'Apples'}>} DISTINCT [Call: Call ID]),Monthname,[Liaison Type]))
	179,5
Apr 2015	95
May 2015	144
Jun 2015	89
Jul 2015	103
Aug 2015	91
Sep 2015	165
Oct 2015	160
Nov 2015	177
Dec 2015	172
Jan 2016	128
Feb 2016	284
Mar 2016	281
Apr 2016	174
May 2016	245
Jun 2016	248
Jul 2016	254
Aug 2016	295
Sep 2016	126

[#9]

Each month does have a certain number of Call IDs as call IDs are created for each interaction. There are times where duplicate call IDs are created on the same date if more than one activity took place during the interaction. These duplicates I do not want to count.

[#10]

Thanks. Yes, they do not look too off. But it is strange as the numbers for August should be the same as the numbers we get for the other thread (238) since this is the same data set.

This is helpful though as I think I would just need to divide by the number of unique MSLs to get the monthly average per MSL?

[sunny_talwar]

That thread is using a slightly different set analysis.

Issue with Counting Distinct

Count(distinct{$<[Liason Type]-={'FME','AML'},[Product: Description]={'Apples'},Objective-={'Presentations'}>} [Call: Call ID])

How to get KPI of average calls per person per month

Count({$<[Liason Type]={'MSL'},[Product: Description]={'Apples'}>} DISTINCT [Call: Call ID])

This difference might have been causing a difference in the count of [Call: Call ID] that we are seeing here.

I guess, we need to take a step back and you need to explain what exactly are you looking to calculate? May be once we know what you are after, we might be able to suggest the best way to handle your issue.

HTH

Best,

Sunny