Nested aggregation not allowed

[RickyLam]

Hi all, 

I am writing an chart expression for computing average daily sales. 

The expression is as follows:

SUM(OrderItem.LineTotal / (Today() - MIN(Order.OrderDate) + 1))

Where the denominator of the above expression means subtracting the current date with the earliest order date (hence the use of MIN() function) for each value of another dimension (e.g. ProductCode) plus 1. 

But I got the "Nested aggregation not allowed" error. 

How should I modify the expression? Thanks. 

[MarcoWedel]

please post some sample data and your expected result

[RickyLam]

Thanks for your response. 

For simplicity, the Order and OrderItem tables are merged together. 

The table below shows three orders, each with different order date, together with the order details:

OrderDate	ProductID	LineTotal
10/5/2023	A	10
10/5/2023	C	20
10/5/2023	D	10
20/6/2023	A	10
20/6/2023	B	30
20/6/2023	E	40
4/7/2023	D	10
4/7/2023	F	70

Suppose current date is 20/7/2023. 

For Product A,  its denominator of daily average sales would be: 

20/7/23 - 10/5/23 (the earliest date) + 1 = 72 days

For Product B,  its denominator of daily average sales would be: 

20/7/23 - 20/6/23 (the earliest date) + 1 = 31 days

Similarly for Product F:

20/7/23 - 4/7/23 + 1 = 17 days

Please advise further. Thanks. 

[MarcoWedel · Accepted solution]

maybe you could try something like 

Sum(LineTotal)/(Today()-Min(OrderDate)+1)

instead?

[RickyLam]

Oh really thanks for your insight. 

Just a very simple change would evade the nested aggregation error.