I’m working on a project that uses a dataset containing thousands of Amazon Connect ctr records over the course of 6 months. I want to show, in a bar graph, the average number of daily calls that each individual agent makes. Applicable data attributes in my dataset are {Agent Name} and {Date of record}. I don’t want to show this data over time, I want each bar to represent an agent’s average daily calls made as a singular value. I realize that creating this requires custom aggregation, but I have tried and failed. I imagined the formula would look something like this:
avgOver(count({Agent Name}), [{Date of record}])
This, however, is just giving me an error like so:
VISUAL_CALC_REFERENCE_MISSING
Just as an example, let’s say one of the agent’s name is Sally, and Sally had made 3 calls per day for 2.5 months and 5 calls per day for a different 2.5 months. On the bar graph that I’m proposing, Sally’s bar would measure at 4. I want to make this same calculation for each agent in my dataset.
Please let me know if any additional info is needed. Thanks in advance!
Thank you for responding. The data is similar to how you lay it out in your example. You suggest setting the value aggregation to average, but your screenshots don’t show that being done. In this case, I don’t know what field should be getting averaged. Intuitively, it should be the count for {agent username}, but I’m not sure how to implement that.
I also see in your screenshot that the date is being used on the x axis. I don’t want the date shown on either the x or y axis. Preferably, I’d like the x axis to be the {agent username} and the y axis show the average number of calls per day.
Can you try this?
Average number of daily calls = avg(count({Agent Name}, [truncDate(“DD”, {Date of record})]))
The inner LAC-A function calculates the number of records for each agent per day. The outer avg function then calculates the average of that number grouped by dimension in your visual (agent name).
Put Agent Name on your x-axis and the above calculated field in your Value field well.
Btw if you use avgOver, you should specify PRE_AGG or PRE_FILTER. If you don’t, all the fields present in your calculated field have to be in your visual. That’s what that VISUAL_CALC_REFERENCE_MISSING error means.
In the above examples, “customAvg” is the calculated field that you gave me. These results are being taken from bar charts where the x-axis field is {Agent Username} and the y-axis field is one of the two fields in the above examples.
I don’t understand how it’s getting these results. The distinct count for dates is 134, so for Agent1, I want 9163 / 134 = 68. How can I achieve this?
My calculated field was counting the number of calls that an agent received on each day that they received calls and then taking the average of those numbers. If they only received calls on 10 days out of 134, it was taking the average of 10 values.
If you want the average to always be based on the total number of days in your dataset, including days when they didn’t receive calls, you have to do this differently.
First Date of Data = minOver({Date of Data}, , PRE_AGG)
Last Date of Data = maxOver({Date of Data}, , PRE_AGG)
Number of Days = dateDiff({First Date of Data}, {Last Date of Data}) + 1
Calls per day = count({Agent Username}) / min({Number of Days})
Then just put “Agent Username” on your x-axis and “Calls per day” on your y-axis.
