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

# Percentage messages with no replies

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

It looks like this:

 QuestionID MsgID 8023138 9457740 8057449 9480070 8057449 9491955 8060905 9460770 8060905 9500963 8210617 9491981 8211332 9491979

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

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.

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

Then to get the percentage I just needed:

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

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

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.