Avg with missing Data (aggr?)

[Anonymous]

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

[sunny_talwar]

May be this

Sum(Menge)/Count(DISTINCT TOTAL Buchungsdatum)

[Anonymous]

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...

[sunny_talwar]

This can be the other way

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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.