7 Replies Latest reply: Feb 27, 2013 8:39 AM by Henric Cronström

# Avg and Stdev to consider nulls values

Hi, guys!

How to get AVG function consider null values inside AGGR funtion?

For example we selected three dates. What we need is:

1) Aggregate Item Sales by Dates. So we have 3 values for each Item. For that purpose I use "AGGR(Sum(Sales),Date,Item)"

2) Calculate AVG from this 3 values.

Problem: If in Source Data there is no row with Sales for that Date, AGGR(Sum(Sales),Date,Item) returns null, instead of Zero.

AVG function make calculation only among values, whitch are not Nulls. So average becomes wrong.

How to solve that problem? It is not so impotant for calculating Average, because there is another way to calculate it, but I need it to behave that way

for STDEV function.

If it is impossible, I dont understand how to make XYZ-analisys work in Qlikview at all.

We thought about unnormalization source data or something, but we think this not the better way.

Source data looks like this

• ###### Re: Avg and Stdev to consider nulls values

Try AGGR(rangesum(0,Sum(Sales)),Date,Item) won't work. Maybe this will: sum(Sales)/count(DISTINCT Date])

• ###### Re: Avg and Stdev to consider nulls values

I already tried this way of thinking. On the picture where "Right average"

As I said It will work for Average, but what to do with Stdev?

• ###### Re: Avg and Stdev to consider nulls values

QlikView does the correct thing here - it divides by the number of possible dates: Possible in relation to the dimensional value. But you want to divide by all dates. Hence, Sum(Sales)/Count(distinct total Date) should do the trick.

HIC

• ###### Re: Avg and Stdev to consider nulls values

I already tried this way of thinking. On the picture where "Right average"

As I said

It will work for Average, but what to do with Stdev?

• ###### Re: Avg and Stdev to consider nulls values

You're right. Then I do not see any other solution than generating the missing combinations and filling these with zeros. (See more on http://community.qlik.com/docs/DOC-3786 in the section "Generating all combinations of several fields - Cartesian product using Join")

But I realize that this may lead to too many records, and so it might not be possible.

HIC