KPI expression for "at least one" doesn't work- Help! (model +details included)

[ioannaiogr]

Hi all!

Back to office after vacations and i'm faced with this challenge.

Long story short, i need to count all case_id 's  that have  at least one "yes" in the result field   for each type as described in my model , in a KPI.  In Bold are the fields of interest.

My model is:

all_incidents:

LOAD RecNo() as Key,

case_id   ,//each case_id can have many incident_id's 
incident_id,
date(floor("Date"),'DD/MM/YYYY') as [Incident Date],
date("Date",'DD/MM/YYYY hh:mm:ss.ffffff') as [Incident Date timestamp]

FROM [$(vPathQVD)/all_incidents.qvd]
(qvd);

left join(all_incidents)
//Details of another table for type A or B which form a group which is not of interest right now. Normally all_incidents //table have rows for types A, B or C so for example case_id=1 can have two rows incident_id=1000 and incident_id=1001 //with types A and B respectively

LOAD
"incident_id" as "Incident_id1",
incident_id as  incidentAorB_id,
"result", //it's a yes or no
"type"

FROM [$(vPathQVD)/typeAorB.qvd]
(qvd)
where "type"<>'C';

left join(all_incidents)
//last incident_id by case_id and type:
LOAD
case_id,

type,

Date(Max([Incident Date timestamp])) AS [Max Date of incident_id],
max("incident_id") as maxAorBincident_id
RESIDENT
all_incidents
GROUP BY
case_id,
type,
year(date(floor([Date]),'DD/MM/YYYY'))
;

For Each vType In FieldValueList('type')

[$(vType)Details]:
LOAD
case_id,
maxAorBincident_id as [ID of last incident of type  $(vType)],
Max Date of incident_id] as [Max Date of incident_id of type $(vType)],
[result] as [Result of last incident of type $(vType)]
RESIDENT
all_incidents
WHERE
"type" = '$(vType)'
AND [incident_id] = maxAorBincident_id
AND [Incident Date timestamp] = [Max Date of incident_id]
AND Len(Trim([Incident Date timestamp]))<>0
;

Next vType;

DROP FIELD [Max Date of incident_id] FROM all_incidents;

The KPI i tried but doesn't seem to work correctly is 

 count(distinct {<[Result of last incident of type A]={'yes'}>+<[Result of last incident of type B]={'yes'}>} [case_id])

and i know it's failing because i made a straight table with columns case_id and dimension =If([Result of last incident of type A]='yes' or [Result of last incident of type B]='yes',1,0) and the  count of flag 1's doesn't equal the KPI expression. I know for a fact the count of flag  0's (when all last results are negative simultaneously for each case_id) is correct.

Sorry for the big post. I was trying to be detailed about this matter.

Waiting for your valuable help!

Thanks in advance.

[rbartley]

Hi,

Since you didn't post your app, I created a simpler version using the following load script:

[RawData]:
Load * Inline [
Case_ID,Incident_ID,Result
1,1000,Y
1,1001,N
1,1002,N
2,1003,N
3,1004,N
4,1005,Y
5,1006,Y
6,1007,Y
7,1008,N
];

[Case_Results]:
Load 
Case_ID,
MaxString([Result]) as "MaxResult"
Resident [RawData]
group by Case_ID;


Drop Table [RawData];

I then created a simple table containing the two fields as dimensions:

And then a KPI, with the expression: 

Count({<[MaxResult]={'Y'}>}Case_ID)

This seems to work.  Could you please try it and let me know if you still have an issue.

[ioannaiogr]

Hi @rbartley  thanks for replying so quickly 😄

The raw data should look like this 

[RawData]:
Load * Inline [
Case_ID,Incident_ID,Result, Type
1,1000,Y,A
1,1001,N,A
1,1002,N,B
2,1003,N,A
3,1004,N,B
4,1005,Y,A
4,1009,Y,B
5,1006,Y,A
6,1007,Y,A
7,1008,N,B
];

Then I need the count of case_id where there is at least one "Y" for type A or B (or both like in case_id=4).

Now case_id=1 won't be counted because the max(incident_id) for type A is a "N" and so is for type B.

For the above data I would need the script and model to count in this KPI a total of  3 (case_ids 4 (A and B), 5 and 6).

I hope i made it more clear now 😕 Waiting for your response 🙂

[rbartley]

I see, in which case you just need to update the load script:

//1.get raw data
[RawData]:
Load * Inline [
Case_ID,Incident_ID,Result, Type
1,1000,Y,A
1,1001,N,A
1,1002,N,B
2,1003,N,A
3,1004,N,B
4,1005,Y,A
4,1009,Y,B
5,1006,Y,A
6,1007,Y,A
7,1008,N,B
];

//2. Join with maximum Incident_ID per Case_ID and Type
Left Join
Load
Case_ID,
Type,
Max(Incident_ID) as "Max Incident_ID"
Resident [RawData]
Group by Case_ID,Type;

//3. restrict to only latest incident per case and type
[Latest Incidents per Type]:
Load 
Case_ID,Incident_ID,Result, Type
Resident [RawData]
Where Incident_ID="Max Incident_ID";

Drop Table [RawData];

//4. get highest string value per case
[Case_Results]:
Load 
Case_ID,
MaxString([Result]) as "MaxResult"
Resident [Latest Incidents per Type]
group by Case_ID;


Drop Table [Latest Incidents per Type];

I have attached the qvf file.

[ioannaiogr]

Hi @rbartley , I've only managed to get back to this now.

I want to also be able to count [MaxResult TypeA] or also for other types, do I do it like

count( {<MaxResult={'y'},Type={'A'}>}distinct case_id)

or something like

//4, get highest string value per case

For Each vType In FieldValueList('type')

[$(vType) Details]:

LOAD
case_id,
max([Result] as "MaxResult $(vType)"
RESIDENT [Latest Incidents Per Type]

where [type]='$(vType)'

group by case_id;

Next vType;

[rbartley]

Hello,

It's not clear to me from your question what  [MaxResult TypeA] is supposed to show.  Could you please provide an example based on the data you used above, preferably in Excel.