I've dealt with this before when the session ID could be repeated, in that case you'd be using a countd instead of count. For that situation one solution is to create a modified [SessionID] for the possible responses. i.e. Yes_ID:
If [Was IOFM used]='Yes' then [Session ID] end
Nulls will be ignored in this count, so you'd get a count if only the yes values. Then
would compute the percent of yes responses.
Your case may be simpler, since you are using a count instead. In that case count([SessionID]) will likely give you the same result as Sum([Number of Records]). I recall one day noticing that Number of Records is a computed field, I laughed out loud when I went into the calculation editor to look at the formula:
That is it, it is the number one. We could use an idea similar to the one above to flag only the yes responses. Is_Yes
If [Was IOFM used]='Yes' then 1 else 0 end
Sum([Is_Yes])/Sum([Number of Records])
should compute this value as needed.
Please let me know if one of these works for you, If not, please post a sample dataset so I can include a workbook in my response. It doesn't need to be your real data, but if you mock something up with a similar structure in excel would make it a lot easier to help.
works perfectly, appreciate the help and quick response.
1 of 1 people found this helpful
Do please post sample data or a packaged workbook in the future. You lucked out this time, but that is the most common reason for a slow responses, no responses and even rude responses on the forum. The more setup you can provide the easier it is to help.
no problem Noah,