# Do Average between two calculated dates

**URL:** <https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485>\
**Category:** Q&A\
**Tags:** error, developer, qls-Arena, quick-sight\
**Created:** [May 7, 2024, 9:21am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485 "2024-05-07T09:21:22Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![sakshisl](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@sakshisl](https://community.amazonquick.com/u/sakshisl)\
**Post date:** [May 7, 2024, 9:21am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/1 "2024-05-07T09:21:22Z")

</div>

I have calculates two dates in my dataset:  
Max Date Per Supplier  
maxOver({invoice\_date}, [{supplier\_number}], PRE\_AGG)

Min Date Per Supplier- which is 6 months before the max date:  
addDateTime(-6, “MM”, {Max Date per supplier})

Now I want to find average of amount within these dates.  
I tried using multiple functions but the average is inaccurate.

Example which did not work:  
avg(  
ifelse(  
{invoice\_date} \>= {Min Date Per Supplier},  
{amount},  
null  
),  
[{supplier\_number}]  
)

Any leads? When I download my data and check it in a excel the average of the items is different.

---

<div class="post-metadata">

**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:** [May 7, 2024, 6:01pm UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/2 "2024-05-07T18:01:31Z")

</div>

Hi @sakshisl,

Do you have any duplicates in your dataset? Can a supplier have only 1 invoice per day? What’s the granularity of your dataset? Does each row of the dataset represent represent one invoice? If you have line items in your dataset, that could also be another source of duplicates.

---

<div class="post-metadata">

**Author:** ![sakshisl](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@sakshisl](https://community.amazonquick.com/u/sakshisl)\
**Post date:** [May 8, 2024, 4:43am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/3 "2024-05-08T04:43:11Z")

</div>

Hi,  
My granularity is [supplier\_number and invoice\_number]. There can be more than one invoices in a day.  
But I do need the average basis supplier\_number.

---

<div class="post-metadata">

**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:** [May 8, 2024, 6:12am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/4 "2024-05-08T06:12:59Z")

</div>

With the way that you wrote your calculated field, when you add it to your Value field well, you still have to select an aggregation. Can you check what aggregation you selected there?

---

<div class="post-metadata">

**Author:** ![sakshisl](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@sakshisl](https://community.amazonquick.com/u/sakshisl)\
**Post date:** [May 8, 2024, 12:47pm UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/5 "2024-05-08T12:47:21Z")

</div>

I am trying to get it as a single KPI with no other fields.

---

<div class="post-metadata">

**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:** [May 8, 2024, 4:27pm UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/6 "2024-05-08T16:27:41Z")

</div>

Your calculated field is calculating the average grouped by supplier.

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/d/9/d9bfb087ba236edc05a74eecd75c0408ba5e3f52.png)

Avg for Supplier 123 = (100+150)/2 = $125  
The $200 doesn’t count since it doesn’t satisfy your condition.

Avg for Supplier 456 = $130

If you want a KPI, it means you want Quick Sight to give you a single value. What do you want Quick Sight to do with the $125 and $130 to give you that single value? Do you want the average, i.e., (125+130)/2? What is your expected result here?

---

<div class="post-metadata">

**Author:** ![sakshisl](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@sakshisl](https://community.amazonquick.com/u/sakshisl)\
**Post date:** [May 9, 2024, 7:48am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/7 "2024-05-09T07:48:41Z")

</div>

Hi David,  
Thanks for your detailed analysis on my query.

The usage of the dashboard is to filter out only ONE supplier number at a time so it should show $125. The users check every supplier one by one and make further decisions based on that.

If multiple supplier numbers are selected, it may show an overall average (125+130)/2.

For my validations, I tried filtering only one supplier, it was able to capture the minimum and maximum date correctly but the average came out incorrect.

---

<div class="post-metadata">

**Author:** ![sakshisl](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@sakshisl](https://community.amazonquick.com/u/sakshisl)\
**Post date:** [May 17, 2024, 6:13am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/8 "2024-05-17T06:13:52Z")

</div>

@David_Wong , could you please help me further in this or let me know if there is any other possible way to do this?

---

<div class="post-metadata">

**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:** [May 17, 2024, 6:48am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/9 "2024-05-17T06:48:12Z")

</div>

Hi @sakshisl,

Can you show me a screenshot of the Value field well for your KPI so that I can see what aggregation is selected there?  
 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/1/0/10cac62c5ec65387caecbc7bddf384e8dc6251b9.png)

---

<div class="post-metadata">

**Author:** ![sakshisl](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@sakshisl](https://community.amazonquick.com/u/sakshisl)\
**Post date:** [May 17, 2024, 9:21am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/10 "2024-05-17T09:21:42Z")

</div>

![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/8/8/883f35f6aa533f84bc8f9290e10041b86ea026d8.png)

The use case is to choose one supplier at a time from filter and the average should be shown for that supplier only. @David_Wong

---

<div class="post-metadata">

**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:** [May 20, 2024, 4:45am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/11 "2024-05-20T04:45:25Z")

</div>

Hi @sakshisl,

Is the result incorrect for all suppliers or only for some suppliers? It will be difficult to troubleshoot this without seeing your raw unaggregated data. The calculated field appears to be correct but there may be other factors in the data that we’re not taking into account. If you can choose one supplier for which the result is incorrect and show the raw data for that supplier, it will help.

You can use Arena to share a sample analysis.

> [@QuickSight Arena -- An embedded instance of QuickSight within the QuickSight Community](https://community.amazonquick.com/t/quicksight-arena-an-embedded-instance-of-quicksight-within-the-quicksight-community/21437):
>
> QuickSight Arena provides you with an embedded instance of QuickSight right within the QuickSight Community. Using Arena, you can: Explore creating QuickSight data visualization dashboards for free Publish dashboards to the Community [Gallery](https://community.amazonquick.com/c/gallery/44) to showcase your skills Include dashboards depicting your use case while posting questions in the [Community Q&A](https://community.amazonquick.com/c/question-and-answer/5) Clone underlying analysis views of published dashboards in order to learn / try answering questions Include dashboards, w…

---

<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:** [June 1, 2024, 3:51am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/12 "2024-06-01T03:51:50Z")

</div>

Hi @sakshisl, I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - [Quick Sight Arena](https://community.amazonquick.com/t/quicksight-arena-an-embedded-instance-of-quicksight-within-the-quicksight-community/21437))

---

<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:** [June 1, 2024, 3:51am UTC](https://community.amazonquick.com/t/do-average-between-two-calculated-dates/29485/13 "2024-06-01T03:51:55Z")

</div>

Archived on June 1, 2024, 3:51am UTC


