KPI with multiple conditions and JOINs in Qlik Sense
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:
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:
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.