# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

## 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

1 Solution

Accepted Solutions
MVP

## Re: Exclude null dimension from Aggr()

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

or this

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

7 Replies
Contributor II

## Re: Exclude null dimension from Aggr()

Hi,

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

=avg(Sales)

Not applicable

## 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.

Contributor II

## Re: Exclude null dimension from Aggr()

Try this

=avg(Aggr(Sales,Period))

Not applicable

## 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.

Contributor II

## Re: Exclude null dimension from Aggr()

Ok use below expression

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

MVP