Avg and Stdev to consider nulls values

[wkolbnik]

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

[Gysbert_Wassenaar]

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

[wkolbnik]

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?

[swuehl]

Are you using a master calender that contains all dates in that range?

I think this should solve your issue.

See for example Henric's blog post here:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

Or search the forum for master calender, there are tons of threads.

[hic]

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

[wkolbnik]

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?

[wkolbnik]

Ok. I'll discover that.

[hic]

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

[Fran_by]

I have a similar problem for Stdev() function.
Solution with generating the missing combinations, not realistic for my data set, too many records!

So mean, correct XYZ-analysis not possible in QlikVIew ?