8 Replies Latest reply: Sep 15, 2017 2:17 AM by Kevin Nagel RSS

    Avg with missing Data (aggr?)

    Kevin Nagel

      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