KPI with multiple conditions and JOINs in Qlik Sense

[Anonymous]

Hi Qlik community.

I need to create KPI. I have a select query which calculates tickets (workorderid) on the first line service desk between specified dates and some other restrictions. It looks like below:

SELECT COUNT(wo.WORKORDERID)

FROM workorder wo

LEFT JOIN WorkOrderStates ws ON wo.WORKORDERID=ws.WORKORDERID

LEFT JOIN statusdefinition sd ON ws.STATUSID=sd.STATUSID

LEFT JOIN WorkOrder_Queue wq ON wo.WORKORDERID = wq.WORKORDERID

LEFT JOIN QueueDefinition qd ON wq.QUEUEID=qd.QUEUEID

LEFT JOIN REQUESTTEMPLATE_LIST tl ON wo.TEMPLATEID=tl.TEMPLATEID

WHERE wo.COMPLETEDTIME >= datetolong('2017-03-01') AND wo.COMPLETEDTIME <= datetolong('2017-04-01') AND qd.QUEUENAME LIKE 'ServiceDesk%' AND (tl.templatename like '%damage%' 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)) AS "Awarie I-linia"

Joins between FROM and WHERE are configured in data model. Tables “wttd” and “td” are also in data model. Dates restrictions are done through general filter in application so I don’t bother to insert them into the script. I don’t know how in Qlik Sense create full KPI expression. Right now KPI looks like below:

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

As you see part [….AND wo.WORKORDERID NOT IN (SELECT……td.taksid is null)] of the sql query is missing in the script.

Could someone please help me add missing past to the KPI script?

[Digvijay_Singh]

May be this -

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

[Anonymous]

Unfortunately it's not a good answer.

According to above there are “and’s” between all conditions. It doesn’t take into account that there is subquery in where clause which excludes some WORKORDERID’s from count. I have tried to subtract two counts but did not get good result.