7 Replies Latest reply: Jan 17, 2017 8:07 AM by Magnus Buseth

# Exclude null dimension from Aggr()

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

• ###### Re: Exclude null dimension from Aggr()

Hi,

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

=avg(Sales)

• ###### Re: Exclude null dimension from Aggr()

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.

• ###### Re: Exclude null dimension from Aggr()

Try this

=avg(Aggr(Sales,Period))

• ###### Re: Exclude null dimension from Aggr()

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.

• ###### Re: Exclude null dimension from Aggr()

Ok use below expression

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

• ###### Re: Exclude null dimension from Aggr()

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

or this

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

• ###### Re: Exclude null dimension from Aggr()

Both of these work, thank you!