3 Replies Latest reply on Apr 13, 2014 3:43 PM by Noah Salvaterra

    Percentage messages with no replies

    Johan De Groot

      I have a table with all messages in a simple forum, including a separate ID for all questions.

       

      It looks like this:

      QuestionIDMsgID
      80231389457740
      80574499480070
      80574499491955
      80609059460770
      80609059500963
      82106179491981
      82113329491979

       

      Some QuestionIDs have multiple MsgIDs (lots of reactions), some have only one (no reaction on original message).

       

      What I would like to know is the percentage of Questions with only 1 MsgID.

           Number of QuestionIDs with 1 MsgID: x%

           Number of QuestionIDs with > 1 MsgID: y%

       

      It's easy to 'see' this in a chart with all QuestionIDs and the count of corresponding MsgIDs, but I have trouble to aggregate this into a percentage.

        • 1. Re: Percentage messages with no replies
          Noah Salvaterra

          I expect there are lots of ways to approach this one, so I went with a simple one.

          With QuestionID in the view, I created a calculated field to count the number of replies.

          Message Count:

          sum([Number of Records])

          you could replace that with a count or even a count distinct if there might be duplicate records that you don't want to count twice.

           

          Then I created an indicator at the QuestionID level based on this count.

          More than one reply:

          if [Message Count]>1 then 1 else 0 end

           

          Then to get the percentage I just needed:

          window_sum([More than one reply])/window_sum(Max(1))

          Computed along QuestionID, this will add up the indicators from the previous step. In the denominator I am also using a window_sum which will give the number of QuestionIDs without qualification. This will result in a value for each question id (the same value), so to clean things up I limited to the first one.

          % Responded:

          if first()==0 then window_sum([More than one reply])/window_sum(Max(1)) end

          and to get the % who didn't respond I subtracted this from 1.

          % Didn't Respond:

          1-[% Responded]

           

          Does that do what you need?

           

          N.

          • 2. Re: Percentage messages with no replies
            Johan De Groot

            Great - thank you very much. This seems indeed the right calculation for me!

             

            I went the wrong way when using the window_sum, but you helped me out on that one.

            • 3. Re: Percentage messages with no replies
              Noah Salvaterra

              Glad I could help. Table calcs are fun, but first they are confusing. You get a hang of partitioning you can make calcs on top of calc on top of calcs with each going in a different direction!

               

              N.