KPI set analysis for "at least one"

[ioannagr]

Hi, I've got the following code which makes tables for each Virus type.

For Each vVirus In FieldValueList('Virus')

[$(vVirus)  Virus Details]:
LOAD
"person ID",
"testID" AS [latest testID for  $(vVirus)],
[Latest testID  date] as [Latest testID date for  $(vVirus)]
RESIDENT
Tests
WHERE
"Virus" = '$(vVirus)';

Next vVirus

I want to give a KPI counting the people that have one or more latest positive test(result can be positive or negative in Tests table).

i'm confused because each person can have multiple positive viruses at once and i'm not sure how i should use the "and/or" operators in set analysis to achieve what i want.

So for example if Virus1=positive for this person and no other virus I want to count this person.

But if I choose Virus1 and Virus3=positive and another person has these,  I want this person to be counted once.

Help me out please!

[sunny_talwar]

Would you be able to share some sample data where we can see the data? Right now I am not sure which fields contains Virus name and which one contains positive or negative results? A sample file can help us look at the issue and provide a better help

[ioannagr]

I cant but I can describe it.

I have a big source table that has info for people's virus tests. It looks like this.

id personID, Virus, testID, dateID,        result

1                    1              400        1/1/2020     pos

1                    2               520         1/1/2020    neg

1                     2              15          2/1/2020   pos

2                    1             48             3/1/2020  neg

etc

so with the above resident loads, I split this table into as many as the viruses are , and contains the latest testID and test date for this person per virus.

@sunny_talwar I wish you can help me!

[sunny_talwar]

So based on the sample data you just showed... what is the expected output with and without selection?

[ioannagr]

KPI without selections for the viruses just show me the count of distinct personIDs.

The difficult part for me is this: because for  the same one person the testIDs for many viruses but on different test dates can be positive, i want to be counted once.

For example : User selects virus 1 :"if virus1=positive on its latest test date but all others on their other test dates are not" count this person

next, user also selects virus 2 : "if virus1=positive on its latest test date but all others on their other test dates are not" and "if virus2=positive on its latest test date but all the rest on their other test dates are not"  count this person once.

I want the kpi to look in aggregation for every person.

I don't think I can do this on my level, I need help for sure 😞 @sunny_talwar 

[ioannagr]

@sunny_talwar i have an idea. Maybe if I added [Latest result for $(Virus)] and did a flag like: if([Latest result for $(Virus)] ='pos',1,0)  as Pos$(Virus) then my KPI

count({<[Latest result for Virus1={'pos'} >+<Latest result for Virus2={'pos'}>+...+<Latest result for VirusN={'pos'} >} distinct [PersonID])  

would give me what i want?

Else, I think the combinations are endless 😕