Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]
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.
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])
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.
Is your issue resolved then?
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.
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).
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!
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])
Why do you think that 62 is the total added together?
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.