Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
maybe you could try something like
Sum(LineTotal)/(Today()-Min(OrderDate)+1)
instead?
please post some sample data and your expected result
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.
maybe you could try something like
Sum(LineTotal)/(Today()-Min(OrderDate)+1)
instead?
Oh really thanks for your insight.
Just a very simple change would evade the nested aggregation error.