2 Replies Latest reply: Mar 10, 2018 1:22 PM by Dragos Simionescu RSS

    Line chart showing Daily and Monthly average

    Dragos Simionescu

      This is probably trivial and I bet lots of users have faced this before, but I can't seem to find a relevant discussion on this.

      I want to be able to show in a line chart both the daily values and a stepped line showing the monthly (or weekely) average.

      The current application analyzes the times spent by employees in the office.

      The data is based on gates access timestamps, and the transactions table looks like this:

      BadgeIDGate_Access
      ID1Day1/Time1
      Day1/Time2
      Day1/Time3
      Day1/Time4 etc
      ID1Day2/Time1
      Day2/Time2 etc
      ID2 etc

      So for each ID, for each day, I have multiple gate access times.

      With a reasonable approximation I will consider the time spent by each employee as the Max-Min for each day. I will then need to aggregate this further up at Month level or Department Level based on the Median

      I calculated this as follows:

      Median( aggr(max(Gate_Access)-min(Gate_Access), [BadgeID-Badge ID], [Gate_Access.autoCalendar.Date]))

      Works perfectly and is correctly aggregated at all needed levels

      I then want to plot this as a daily line chart, but I also want to show the Monthly average (as a stepped line).

      I was able to calculate the monthly average as follows:

      Avg(aggr(Median( aggr(max(Gate_Access)-min(Gate_Access), [BadgeID-Badge ID], [Gate_Access.autoCalendar.Date])), [Gate_Access.autoCalendar.YearMonth]))

       

      However, this only calculates one value, against the start of the Month, and looks like this on the line chart  - the red dot (the yellow line is a Window average):

      Screenshot 2018-03-10 09.12.34.png

      How can I write the expression so it uses the day granularity , but using the monthly average?