topic Re: Avg and Stdev to consider nulls values in QlikView

Avg and Stdev to consider nulls values

wkolbnik — Wed, 27 Feb 2013 10:36:43 GMT

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

Gysbert_Wassenaar — Wed, 27 Feb 2013 10:54:23 GMT

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

wkolbnik — Wed, 27 Feb 2013 11:09:17 GMT

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

swuehl — Wed, 27 Feb 2013 11:42:33 GMT

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.

Re: Avg and Stdev to consider nulls values

hic — Wed, 27 Feb 2013 12:38:56 GMT

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

wkolbnik — Wed, 27 Feb 2013 13:03:05 GMT

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

wkolbnik — Wed, 27 Feb 2013 13:11:26 GMT

Ok. I'll discover that.

Re: Avg and Stdev to consider nulls values

hic — Wed, 27 Feb 2013 13:39:49 GMT

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

Re: Avg and Stdev to consider nulls values

Fran_by — Mon, 16 Nov 2020 09:15:50 GMT

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 ?