KPI with multiple nested conditions

[Anonymous]

Hello Qlik community.

I need to create KPI which calculates tickets (workorderid).

KPI has several nested conditions.

SQL query for this count looks as below:

SELECT COUNT(wo.WORKORDERID) FROM wo

LEFT JOIN ws ON wo.WORKORDERID=ws.WORKORDERID

LEFT JOIN sd ON ws.STATUSID=sd.STATUSID

LEFT JOIN wq ON wo.WORKORDERID = wq.WORKORDERID

LEFT JOIN qd ON wq.QUEUEID=qd.QUEUEID

LEFT JOIN tl ON wo.TEMPLATEID=tl.TEMPLATEID

WHERE

qd.QUEUENAME LIKE 'ServiceDesk%'

AND (tl.templatename like '%awarie%' OR tl.templatename like '%default%' or tl.templatename like '%reset%')

AND wo.title not like '%probid%' and sd.ispending=0

AND wo.WORKORDERID NOT IN (SELECT wttd.workorderid FROM WorkOrderToTaskDetails wttd

                           LEFT JOIN TaskDetails td ON wttd.TASKID=td.TASKID

                           LEFT JOIN QueueDefinition qd ON td.GROUPID=qd.QUEUEID

                           WHERE qd.QUEUENAME NOT LIKE 'Servi%' or td.taskid is null) ;

KPI counts OK in version below:

Count({$<QUEUENAME={'ServiceDesk*'}, TEMPLATENAME={'*awarie*','*default*','*reset*'}, TITLE-={'*probid*'}, ISPENDING={'0'}>} Distinct WORKORDERID)

but whe I try to add two more conditions like below:

Count({$<QUEUENAME={'ServiceDesk*'}, TEMPLATENAME={'*awarie*','*default*','*reset*'}, TITLE-={'*probid*'}, ISPENDING={'0'},

         QUEUENAME-={'Servi*'} or Len(Trim(TASKID))=0>} Distinct WORKORDERID)

result is not OK.

As you see there is subquery which excludes some workorderids from count and I don't know how to write it in KPI script.

[Anonymous]

Sunny,

It is one sheet in attached application.

Unknown is 1st KPI “Breakdown 1-st line”.

SQL query you see at the top of this discussion is for 1st KPI and gives 29623 workorderids. This number should be in first KPI.

2nd KPI “All breakdown” gives correct number 88625.

KPI 3rd “% done on 1st line” is KPI1/KPI2.