1 Reply Latest reply: Apr 23, 2018 1:14 PM by Digvijay Singh RSS

    KPI with multiple conditions and JOINs in Qlik Sense

    Andrzej Kisielewicz

      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?