6 Replies Latest reply: Feb 13, 2018 7:05 AM by Massimo Favaro RSS

    Help with a graph (average number of interaction of users who made atleast an interaction)

    Massimo Favaro

      Hello everyone,

      I am trying to create a column chart, but it's a little complicated for me.

      Let me explain my data:

       

      I have a table with:
      request_date : the date of the interaction

      id : the id of the interaction

      open_id : id of the user

      request_date.Year : the year of request_date

       

      What I'd like to achieve is a column chart, with the interaction year as X axis, and the average number of action performed by users that have made atleast an interaction (in that year).

       

      COUNT(id) / COUNT(DISTINCT(open_id))

      is clearly not correct since it should give me the number of actions in a year divided by the total distinct amount of open_id across all years, I guess.

       

      What should the formula be like?