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:

      Datasrc.PNG

       

      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]}

       

      ActiveUsers

      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