Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm having some problems using Aggr() on data imported from Excel. Aggr() divides by 13 periods, instead of 12. I suspect the 13th period might be a blank row in my Excel file (period = NULL)?
Please see the screenshot below for an example. I have included an Excel file with my data.
How can I make Aggr() divide by 12 periods instead of 13 in this case?
sum(Sales)/count(Period) = 136/12 = 11,33
avg(aggr(sum(Sales),Period)) = 136/13 = 10,46
How about this?
Avg({<Period = {'*'}>}Aggr(Sum(Sales), Period))
or this
Avg(Aggr(If(Len(Trim(Period)) > 0, Sum(Sales)), Period))
Hi,
No need to use aggr function.. Just use Avg() function it will work
=avg(Sales)
I should have specified this, but my actual data is more complicated, so I'm required to use the Aggr() function. I have simplified this example to make it easier to explain.
Try this
=avg(Aggr(Sales,Period))
This works in this specific case, but not if I have multiple sales per period. Thank you for your help though.
I believe the source of the problem is that the Aggr() function includes an extra period=NULL when it takes the average, and I'm trying to understand how to exclude that.
Ok use below expression
=avg({<Period={'*'}>} Sales)
How about this?
Avg({<Period = {'*'}>}Aggr(Sum(Sales), Period))
or this
Avg(Aggr(If(Len(Trim(Period)) > 0, Sum(Sales)), Period))
Both of these work, thank you!