Exclude null dimension from Aggr()

[#1]

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

[khalander]

Hi,

No need to use aggr function.. Just use Avg() function it will work

=avg(Sales)

[#3]

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.

[khalander]

Try this

=avg(Aggr(Sales,Period))

[#5]

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.

[khalander]

Ok use below expression

=avg({<Period={'*'}>} Sales)

[sunny_talwar · Accepted solution]

How about this?

Avg({<Period = {'*'}>}Aggr(Sum(Sales), Period))

or this

Avg(Aggr(If(Len(Trim(Period)) > 0, Sum(Sales)), Period))

[#8]

Both of these work, thank you!