topic Re: Avg with missing Data (aggr?) in QlikView

Avg with missing Data (aggr?)

Anonymous — Mon, 26 Jan 2026 18:19:17 GMT

Hello experts,

I have a Problem with calculating the right avg values:

The Problem in short is as follows - There is an article, that only consumption for 1 day in a month. let's say 01.01.2017.

In the datamodle there are only rows, where there had been consumption.

I want to calculate the average daily consumption

In that case - if I use the avg function the formula will return 1, as it only has one datapoint. It should however divide the consuption by the number of days - 31.

the data model has a a seperate table with calender Dates, which is connected to the consumption Dates.

I tried using the formula as follows:

avg(total aggr(Menge, Datum))

but it does not understand, that I Need the missing Dates filled up with data ...

Thankful for any ideas on this.

Kind regards

Kevin

Re: Avg with missing Data (aggr?)

sunny_talwar — Wed, 13 Sep 2017 14:59:05 GMT

May be this

Sum(Menge)/Count(DISTINCT TOTAL Buchungsdatum)

Re: Avg with missing Data (aggr?)

Anonymous — Wed, 13 Sep 2017 15:34:45 GMT

Hi Sunny,

thank you for your quick Response.

Doesn't this solution burry the possibility of miscalculation if there's a Buchungsdatum missing? for example if we'd have a selection, where there are not all Buchungsdatum given, or worse if there was a single Buchungsdatum missing for all data. That would lead to wrong calculations. I am searching expecially for a solution, that would be applicable for stdev(), since that also uses averages. That actually is the Problem behind the question. I Need to calculate the Standard Deviation for a set of values, per article. And most articles doen't have consumptions on every day. Which leads to wrong calculated values...

Re: Avg with missing Data (aggr?)

sunny_talwar — Wed, 13 Sep 2017 15:37:50 GMT

This can be the other way

Sum(Menge)/(Floor(MonthEnd(Max(Buchungsdatum))) - MonthStart(Max(Buchungsdatum))+1)

Re: Avg with missing Data (aggr?)

Anonymous — Wed, 13 Sep 2017 16:00:09 GMT

but this wouldn't be of any use for the stdev, would it?

Re: Avg with missing Data (aggr?)

sunny_talwar — Wed, 13 Sep 2017 16:05:08 GMT

Not sure I understand why.... what is the output that you are looking to get from your sample?

Re: Avg with missing Data (aggr?)

Anonymous — Wed, 13 Sep 2017 16:23:51 GMT

I'd like to calculate the stdev per article on a weekly Basis.

That at the Moment doesn't work out, because I don't know how to skript the set Analysis in that way, that it calculates the Standard Deviation correctly... Standard Deviation

Re: Avg with missing Data (aggr?)

sunny_talwar — Wed, 13 Sep 2017 16:26:00 GMT

I guess I am trying to understand what are the numbers you are looking to get within the attached sample?

Re: Avg with missing Data (aggr?)

Anonymous — Fri, 15 Sep 2017 06:17:42 GMT

Thank you for your help. I decided to go with a different solution. I used an if-Statement to have '0' as a result of the sum calculation, in order to get values for Dates, where there had been no consumption.

Then I put it in a Pivot table and used the Dates in the column. That i exported via a makro and imported back into qvw.