Qlik Community

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

aggr_example.JPG

1 Solution

Accepted Solutions
MVP
MVP

Re: Exclude null dimension from Aggr()

How about this?

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

or this

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

7 Replies
khalander
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.

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

khalander
Contributor II

Re: Exclude null dimension from Aggr()

Ok use below expression

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

MVP
MVP

Re: Exclude null dimension from Aggr()

How about this?

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

or this

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

Not applicable

Re: Exclude null dimension from Aggr()

Both of these work, thank you!