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