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]
May be this:
Avg(Aggr(Count({$<[Liason Type]={'MSL'},[Product: Description]={'Apples'}>} DISTINCT [Call: Call ID]), [Datetime.autoCalendar.Month], PersonIDDimension))
Appreciate the assistance!
This is on the right track. I now get a value returned for the KPI. However, the value looks like it is each month's average added together. I can take care of this by dividing the value by the number of months. However, I have data for two years and want to be able to be able to show this KPI for 2015 and 2016 separately.
Any suggestions on how to accomplish this?
Not sure what you want exactly, but may be like this:
For 2015
Avg({<Year = {2015}>}Aggr(Count({$<[Liason Type]={'MSL'},[Product: Description]={'Apples'}>} DISTINCT [Call: Call ID]),[Datetime.autoCalendar.Month], PersonIDDimension))
For 2016
Avg({<Year = {2016}>}Aggr(Count({$<[Liason Type]={'MSL'},[Product: Description]={'Apples'}>} DISTINCT [Call: Call ID]),[Datetime.autoCalendar.Month], PersonIDDimension))
Thanks. When I use use either of these scripts, I get a value of zero. I am using 'Liaison Type' as my PersonIDDimension, is this OK? This field indicates the type of person that performed the call.
I believe so. What is your year field called? Year or something else? Did you replace the above year field name with the field name you have within your application?
I was trying to use the Qlik generated year field, '[Datetime.autoCalendar.Year]' however, I do have a have a 'DateTime' field that contains the date and time in the following format: MM/MM/YYYY XX:XX
Should I use the 'DateTime' field in place of 'Year' in the suggested script?
Yes, I would suggest you to try that out and see if that helps:
Year(DateTime) as Year
Sorry for the novice question. Where should I place this script? When I try in the fx editor on the data, I get an error in script...
Within the table where you have the date field
LOAD Date,
Year(Date) as Year,
...
FROM ...