I have a database for results of respiratory virus tests. Each test checks for multiple virus (7) and can be done on different sample specimens. So, for each sample (ie each patient tested), the database has 8 rows: one that contains the sample specimens and 7 that informs the result for each of the 7 viruses (positive/negative).
What I want to get is the number of samples/patientes that have any positive result.
The database looks something like this (there are more rows with things like name, age, various dates for different stages in the process, etc):
| Patient ID | Week | Test name | Result |
|---|---|---|---|
| 1 | 1 | Sample | nasopharyngeal swab |
| 1 | 1 | Flu A | negative |
| 1 | 1 | Flu B | negative |
| 1 | 1 | Adv | positive |
| 1 | 1 | Mpv | negative |
| 1 | 1 | Parainf | negative |
| 1 | 1 | RSV | positive |
| 1 | 1 | Rhinovirus | negative |
| 2 | 1 | Sample | nasopharyngeal swab |
| 2 | ... | ... | ... |
The DB currently has 60k observations (rows) and can easily get to 200k. Also, it gets frequently updated with new data, so the solution I'm looking for should be susceptible to as much automation as possible.
I use pivottables and pivotcharts to represent various statistics. I have the positivity rate for each of the viruses, but I need to get the global positivity, or the amount of samples that have any of the 7 results positive from the total amount of samples processed.
Ideally, I would want this global positivity rate to somehow be included in the per-virus-positivity pivottable so that I can gent it on the graph with all other 7 individual positivty rates.
(For that one, I actually used 2 pivottables: the first is filtered by positive result, and has 'Week' in rows, 'test name' in columns, and count of 'result' as values ["nasopharyngeal swab" is filtered out from the test name variable]. The second one is not filtered and has the same other parameters. Then I built a normal table on which I calculate each positivity, by week, just by dividing the second pivottable value by the one on the firs pivottable.)
I have given it a lot of thought and have tried searching online but to no success.
In case it matters, Im running Excel 2019
