# Finding the Date with the most number of orders for every customer

**URL:** <https://community.amazonquick.com/t/finding-the-date-with-the-most-number-of-orders-for-every-customer/44944>\
**Category:** Q&A\
**Tags:** visuals, calculations, Business-Intelligence-Engineer, quick-sight\
**Created:** [April 13, 2025, 1:25pm UTC](https://community.amazonquick.com/t/finding-the-date-with-the-most-number-of-orders-for-every-customer/44944 "2025-04-13T13:25:53Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ali\_B](https://avatars.discourse-cdn.com/v4/letter/a/e19b73/32.png) [@Ali\_B](https://community.amazonquick.com/u/Ali_B)\
**Post date:** [April 13, 2025, 1:25pm UTC](https://community.amazonquick.com/t/finding-the-date-with-the-most-number-of-orders-for-every-customer/44944/1 "2025-04-13T13:25:53Z")

</div>

How can I find the dimension with the most/least value by a particular calculated measure, partinied by another dimension?

I have this calculated field that returns the date with the most orders by a customer (phone\_number):

```auto
firstValue( SHIFTDATE, [distinct_count(OrderKey) DESC],[{PHONE_NUMBER}])

```

When I add it to the table below:

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/6/4/643f9bf1a02dfc02e001b5a9c2107eeb8955c45b.png)

I get this error:

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/7/6/76027c8cd56c38917f16bf32f7c824a94cc36f49.png)

If I add the {SHIFTDATE} field to the table, the table works, but now I have a row for every Shiftdate and phone number, which I don’t want.

---

<div class="post-metadata">

**Author:** ![shravya](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/shravya/32/9969_2.png) [@shravya](https://community.amazonquick.com/u/shravya)\
**Post date:** [April 14, 2025, 1:26am UTC](https://community.amazonquick.com/t/finding-the-date-with-the-most-number-of-orders-for-every-customer/44944/2 "2025-04-14T01:26:14Z")

</div>

Hello @Ali_B

Can you try adding “SHIFTDATE” (date field) under values and hide the field?

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/0/6/06d207e3eadf4834db7e17a0a6e8be1c3b86dbb9.png)

Please let me know if that helped

Thank you,  
Shravya

---

<div class="post-metadata">

**Author:** ![Ali\_B](https://avatars.discourse-cdn.com/v4/letter/a/e19b73/32.png) [@Ali\_B](https://community.amazonquick.com/u/Ali_B)\
**Post date:** [April 14, 2025, 7:44am UTC](https://community.amazonquick.com/t/finding-the-date-with-the-most-number-of-orders-for-every-customer/44944/3 "2025-04-14T07:44:36Z")

</div>

If I do that, it works, but now every row is repeated for how many {SHIFTDATE} there are per Phone number. So this does not solve the problem

---

<div class="post-metadata">

**Author:** ![Xclipse](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/xclipse/32/20455_2.png) [@Xclipse](https://community.amazonquick.com/u/Xclipse)\
**Post date:** [May 13, 2025, 5:19am UTC](https://community.amazonquick.com/t/finding-the-date-with-the-most-number-of-orders-for-every-customer/44944/4 "2025-05-13T05:19:17Z")

</div>

Hi @Ali_B

When using the [firstValue](https://docs.aws.amazon.com/quicksight/latest/user/firstValue-function.html) function, any fields used in the calculation must be included in the visual to display the value, and you can then hide them if you don’t need them to be visible.

---

<div class="post-metadata">

**Author:** ![Xclipse](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/xclipse/32/20455_2.png) [@Xclipse](https://community.amazonquick.com/u/Xclipse)\
**Post date:** [May 16, 2025, 4:35am UTC](https://community.amazonquick.com/t/finding-the-date-with-the-most-number-of-orders-for-every-customer/44944/5 "2025-05-16T04:35:39Z")

</div>

Hi @Ali_B

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

---

<div class="post-metadata">

**Author:** ![Brett](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/brett/32/36494_2.png) [@Brett](https://community.amazonquick.com/u/Brett)\
**Post date:** [May 22, 2025, 4:02pm UTC](https://community.amazonquick.com/t/finding-the-date-with-the-most-number-of-orders-for-every-customer/44944/6 "2025-05-22T16:02:56Z")

</div>

Hi @Ali_B,  
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you