3 Replies Latest reply on Sep 11, 2019 3:00 AM by Diego Parker

    Yearly average of calculated field

    Jeroen Meijerink

      Hello everyone,


      My table looks like this:



      I want to calculate an yearly average of the number of active users but I can't figure it out.

      When I can't use fixed and avg(Count([Active Users Count])) because I can't aggregate aggregates.


      I want an extra column showing the monthly averages per year. So, per year (count of active users) / amount of months in that year. For 2017 it will be 15.5, for 2018 it will be 26.3 and for 2019 it will be 35.9.


      I tried the window_avg function but I still can't get it to work properly.


      My calcs:


      Conv per csr per month

      { FIXED DATEPART('month',[Sent Date Time]),[CSR name]: [Conversations]}



      IF [Conv per csr per month] >= 20 THEN true else

      false END


      Active Users Count

      If [ActiveUsers] then 1 else 0 end


      Sent Date

      Date([Sent Date Time])


      Count of Active Users

      Count([Active Users Count])



      I'd love to hear suggestions on how to figure this out.

      Thanks in advance!


      Message was edited by: Jeroen Meijerink