# Attempting to find average numbers of calls made daily by individual agents **URL:** \ **Category:** Q&A\ **Tags:** error, quick-sight\ **Created:** [September 8, 2023, 7:13pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839 "2023-09-08T19:13:05Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Max\_English](https://avatars.discourse-cdn.com/v4/letter/m/ba8739/32.png) [@Max\_English](https://community.amazonquick.com/u/Max_English)\ **Post date:** [September 8, 2023, 7:13pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/1 "2023-09-08T19:13:05Z")
Hello! First post on this forum 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! ---
**Author:** ![neelay](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/neelay/32/12168_2.png) [@neelay](https://community.amazonquick.com/u/neelay)\ **Post date:** [September 9, 2023, 12:44am UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/2 "2023-09-09T00:44:48Z")
@Max_English what’s your data row look like (columns) If you have data like | agent | call time | customer | issue | | --- | --- | --- | --- | | Sally | 23-08-01 00:01:01 | John | refund | | Sally | 23-08-02 00:02:01 | Jack | refund | you can just select the value aggregation to average and time aggregation to month. ![Screenshot from 2023-09-08 20-43-00](https://d1wl4jav5dp96l.cloudfront.net/original/2X/a/ade3116414a8ccab694c4b0051084de6bb92f4eb.png) ![Screenshot from 2023-09-08 20-43-26](https://d1wl4jav5dp96l.cloudfront.net/original/2X/e/e3282201370e1d03fd8b5eb6e407183433ba5550.png) ---
**Author:** ![Max\_English](https://avatars.discourse-cdn.com/v4/letter/m/ba8739/32.png) [@Max\_English](https://community.amazonquick.com/u/Max_English)\ **Post date:** [September 9, 2023, 4:35am UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/3 "2023-09-09T04:35:42Z")
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. Thank you! ---
**Author:** ![David\_Wong](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/david_wong/32/15630_2.png) [@David\_Wong](https://community.amazonquick.com/u/David_Wong)\ **Post date:** [September 9, 2023, 7:44am UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/4 "2023-09-09T07:44:06Z")
Hi @Max_English, 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. ---
**Author:** ![Max\_English](https://avatars.discourse-cdn.com/v4/letter/m/ba8739/32.png) [@Max\_English](https://community.amazonquick.com/u/Max_English)\ **Post date:** [September 9, 2023, 5:13pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/5 "2023-09-09T17:13:32Z")
Hi David! I tried the solution which you proposed, but I’m getting this error: Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed. If I use the avgOver function instead which does allow nested aggregate functions, I get the same error in my initial post. Thank you! ---
**Author:** ![David\_Wong](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/david_wong/32/15630_2.png) [@David\_Wong](https://community.amazonquick.com/u/David_Wong)\ **Post date:** [September 9, 2023, 7:24pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/6 "2023-09-09T19:24:56Z")
That’s weird. Can you show a screenshot of your calculated field? Mine works fine: ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/5/5dd8ab6c4116cca624667a0af6beda19a9b2bc20.png) ---
**Author:** ![David\_Wong](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/david_wong/32/15630_2.png) [@David\_Wong](https://community.amazonquick.com/u/David_Wong)\ **Post date:** [September 9, 2023, 7:35pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/7 "2023-09-09T19:35:28Z")
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. ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/2/265cc76ef1a30a364712f368f30e64fe2ce26ebf.png) > **[Using level-aware calculations in Amazon QuickSight - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/level-aware-calculations.html#level-aware-calculations-window)** > > Use level-aware calculations in an Amazon QuickSight analysis to insert a window or aggregate function into the order of evaluation. ---
**Author:** ![Max\_English](https://avatars.discourse-cdn.com/v4/letter/m/ba8739/32.png) [@Max\_English](https://community.amazonquick.com/u/Max_English)\ **Post date:** [September 9, 2023, 9:02pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/8 "2023-09-09T21:02:32Z")
Sure! Here it is ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/b/bca9f32f22f396017117230f9eec0e44b01151b4.png) ---
**Author:** ![David\_Wong](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/david_wong/32/15630_2.png) [@David\_Wong](https://community.amazonquick.com/u/David_Wong)\ **Post date:** [September 9, 2023, 10:56pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/9 "2023-09-09T22:56:21Z")
You misplaced some of the brackets in your calculated field. Try this: `avg(count({Agent Username}, [truncDate("DD", {Date of Data})]))` ---
**Author:** ![Max\_English](https://avatars.discourse-cdn.com/v4/letter/m/ba8739/32.png) [@Max\_English](https://community.amazonquick.com/u/Max_English)\ **Post date:** [September 11, 2023, 3:05pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/10 "2023-09-11T15:05:56Z")
Hi David This function works, but it’s not giving me data that I expect. Here are a few examples: -Agent1 count({Agent Username}) = 9163 customAvg = 916 -Agent2 count{{Agent Username}) = 1991 customAvg = 895 -Agent3 count({Agent Username}) = 47 customAvg = 957 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? Have a nice day! ---
**Author:** ![David\_Wong](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/david_wong/32/15630_2.png) [@David\_Wong](https://community.amazonquick.com/u/David_Wong)\ **Post date:** [September 11, 2023, 11:15pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/11 "2023-09-11T23:15:29Z")
Hi @Max_English, Do you want to always divide by 134 even if there are dates when an agent doesn’t have any records? ---
**Author:** ![Max\_English](https://avatars.discourse-cdn.com/v4/letter/m/ba8739/32.png) [@Max\_English](https://community.amazonquick.com/u/Max_English)\ **Post date:** [September 12, 2023, 2:29pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/12 "2023-09-12T14:29:59Z")
Yes, that is what I’m interested in ---
**Author:** ![David\_Wong](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/david_wong/32/15630_2.png) [@David\_Wong](https://community.amazonquick.com/u/David_Wong)\ **Post date:** [September 12, 2023, 11:55pm UTC](https://community.amazonquick.com/t/attempting-to-find-average-numbers-of-calls-made-daily-by-individual-agents/17839/13 "2023-09-12T23:55:09Z")
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.