Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get KPI of average calls per person per month

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]

51 Replies
Not applicable
Author

Thanks for the continued response. What I need to be able to do within Qlik is the following:

1. Using the data set provided, I need to be able to count distinct 'Call ID' for the 'Apples' product.

2. I then need to be able to count distinct 'Call ID' for the 'Apples' product but only count the 'Call ID' where the 'Liaison Type'=MSL

3. I need to be able to count distinct 'Call ID' for the 'Apples' product where the 'Liaison Type'=MSL for interactions that exclude the 'Objective'=Presentations.

I what to be able to display this on a line graph and show the total number of interactions per month and year.

Hopefully that helps clarify things.

Not applicable
Author

So I have determined that the [Datetime.autoCalendar.Year] field is working as I can use this field as a selection box and the correct numbers and months are displayed. However, when I use the below script in a KPI object, the total count of interactions for 2016 is not shown. The value shown is 2016 and 2015 combined. Any thoughts on to why this script is not working?

Count({$<[Datetime.autoCalendar.Year]={'2016'},[Product: Description]={'Oranges'}, [Liason Type]={'MSL','RAD'},[Interaction Type]={'Face to Face','Phone'}>} DISTINCT [Call: Call ID])

Not applicable
Author

I was able to solve this issue. The script is working correctly here. The issue revolves around calendar year and fiscal year. I was expecting fiscal year numbers but the calculation was showing calendar year values. It appears that I need to create a fiscal year dimension.

sunny_talwar

Is your issue resolved then?

Not applicable
Author

No, the script still does not calculate the KPI correctly. The value displayed is still adding each monthly average together for the final value. So for example, it is adding April+May+June+July+August+Sept=KPI. This is not what I want. I want the overall monthly average of interactions for 2016 to date.

swuehl
MVP
MVP

I guess it would be more than helpful if you could post a small sample QVF that demonstrates your issue (could contain mock up or scambled data, if needed).

Not applicable
Author

I posted a sample QVF. The data is sample data so the association was not that great but it should work. The KPI object is there and shows a value of 62 (which is the total of interactions from each month added together). What I am expecting from this KPI based on the script is to show a value of 10.3 (which would be 62/6 because there is 6 month's worth of data available).

Hopefully this makes sense.

Thanks!

sunny_talwar

How about something like this?

Avg({<[Datetime.autoCalendar.YearMonth]={'2016-Apr','2016-May','2016-Jun','2016-Jul','2016-Aug','2016-Sep'}>}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(DISTINCT {<[Datetime.autoCalendar.YearMonth]={'2016-Apr','2016-May','2016-Jun','2016-Jul','2016-Aug','2016-Sep'}>} [Datetime.autoCalendar.Month])

swuehl
MVP
MVP

Why do you think that 62 is the total added together?

Not applicable
Author

I am assuming because I have calculated the actual monthly averages by hand and they come out to the following:

April = 15.1

May = 14.3

June = 16.9

July = 12.9

Aug = 11.5

Sept = 4.5

This total would be 75. Therefore the 62 is not accurate but it is in the ballpark.