# Sub-query in Quick sight

**URL:** <https://community.amazonquick.com/t/sub-query-in-quick-sight/23060>\
**Category:** Q&A\
**Tags:** analysis, Business-Intelligence-Engineer, quick-sight\
**Created:** [December 26, 2023, 12:42am UTC](https://community.amazonquick.com/t/sub-query-in-quick-sight/23060 "2023-12-26T00:42:35Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Annab](https://avatars.discourse-cdn.com/v4/letter/a/2bfe46/32.png) [@Annab](https://community.amazonquick.com/u/Annab)\
**Post date:** [December 26, 2023, 12:42am UTC](https://community.amazonquick.com/t/sub-query-in-quick-sight/23060/1 "2023-12-26T00:42:35Z")

</div>

Hi,

I have transactional data set.  
Each transaction is linked to employee.  
I would like to present a report with daily average number of transactions per employee.  
How can it be achieved in quicksight?

I need to have average function over daily count function.

Thanks

---

<div class="post-metadata">

**Author:** ![apjvinod](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@apjvinod](https://community.amazonquick.com/u/apjvinod)\
**Post date:** [December 26, 2023, 2:49am UTC](https://community.amazonquick.com/t/sub-query-in-quick-sight/23060/2 "2023-12-26T02:49:29Z")

</div>

Hi @Annab

Quick Sight supports simple [avg()](https://docs.aws.amazon.com/quicksight/latest/user/avg-function.html) and level aware [avgOver()](https://docs.aws.amazon.com/quicksight/latest/user/avgOver-function.html) functions .

Please check our [DemoCentral website](https://democentral.learnquicksight.online/#Dashboard-FeatureDemo-Calculation-Level-Aware-Calculations) for more detailed examples of Quick Sight functions and sample dashboards.

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”.

This will help the community to find guidance and answers to similar question. Thank you!

---

<div class="post-metadata">

**Author:** ![Asem](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/asem/32/3363_2.png) [@Asem](https://community.amazonquick.com/u/Asem)\
**Post date:** [December 28, 2023, 5:15pm UTC](https://community.amazonquick.com/t/sub-query-in-quick-sight/23060/3 "2023-12-28T17:15:48Z")

</div>

Hi, I am marking @apjvinod reply as a solution. Please let us know if you need further assistance.

---

<div class="post-metadata">

**Author:** ![Annab](https://avatars.discourse-cdn.com/v4/letter/a/2bfe46/32.png) [@Annab](https://community.amazonquick.com/u/Annab)\
**Post date:** [December 30, 2023, 8:09pm UTC](https://community.amazonquick.com/t/sub-query-in-quick-sight/23060/4 "2023-12-30T20:09:53Z")

</div>

Hi @Asem,

I have used the avgOver and was able to calculate the daily average per employee.  
avgOver(count{trx\_id}),[{employye\_name}])

However, in order to present it I need to add to the table the employee\_name and trx\_date.  
Once I remove the trx\_date from the view, the average is calculated per day and presents exactly same values as the count per day.

So, I tried to use the following calculation:  
avgOver(count{trx\_id}),[{employye\_name},{trx\_dae}])  
and when using in the table only employee name and daily average measure got the following error:  
VISUAL\_CALC\_REFERENCE\_MISSING

How can I have a table presenting the employee\_name and average\_daily\_number\_of\_transactions?

Thanks,  
Anna

---

<div class="post-metadata">

**Author:** ![Asem](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/asem/32/3363_2.png) [@Asem](https://community.amazonquick.com/u/Asem)\
**Post date:** [December 31, 2023, 12:40am UTC](https://community.amazonquick.com/t/sub-query-in-quick-sight/23060/5 "2023-12-31T00:40:20Z")

</div>

Hi @Annab,  
Can you try using the following calculation:  
avg(count({trx\_id},[{employye\_name},{trx\_id}, {trx\_dae}]))

Let us know if this helps.  
Thanks,  
Asem.

---

<div class="post-metadata">

**Author:** ![Annab](https://avatars.discourse-cdn.com/v4/letter/a/2bfe46/32.png) [@Annab](https://community.amazonquick.com/u/Annab)\
**Post date:** [December 31, 2023, 1:45am UTC](https://community.amazonquick.com/t/sub-query-in-quick-sight/23060/6 "2023-12-31T01:45:23Z")

</div>

Hi @Asem

I did the following:  
avg(count({trx\_id},[{employye\_name},{trx\_dae}]))  
and it worked.

Thank you!