Do Average between two calculated dates

Question & Answer
, ,
1

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.

2

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.

3

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.

4

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?

1 Like
5

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

6

Your calculated field is calculating the average grouped by supplier.

image
image695×142 3.27 KB

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 QuickSight to give you a single value. What do you want QuickSight 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?

1 Like
7

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.

8

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

9

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

10

image
image323×551 19.7 KB

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

11

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.

12

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 - QuickSight Arena)