Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

wkolbnik
New Contributor

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.

Qlikview question.png

Source data looks like this

Qlikview-Data structure.png

Tags (3)
7 Replies

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])


talk is cheap, supply exceeds demand
wkolbnik
New Contributor

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?

MVP
MVP

Re: Avg and Stdev to consider nulls values

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.

Employee
Employee

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.

Null_In_Average.png

HIC

wkolbnik
New Contributor

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?

wkolbnik
New Contributor

Re: Avg and Stdev to consider nulls values

Ok. I'll discover that.

Employee
Employee

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

Community Browser