11 Replies Latest reply: Jun 12, 2018 8:03 AM by Sunny Talwar RSS

    Include null in KPI set analysis expression

    Andrzej Kisielewicz

      Hello,

      I have following query

       

      select count(wo.WORKORDERID)

      from workorder wo

      left join ws….

      left join cd….

      where (ws.ISOVERDUE=1)

      and ((cd.CATEGORYNAME = 'AA')

        OR (cd.CATEGORYNAME = 'BB')

        OR (cd.CATEGORYNAME IS NULL));

       

      I want to write KPI based on above query. KPI is OK when I count:

       

      Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB'}>} Distinct WORKORDERID)

       

      But this version is wrong:

       

      Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB', IsNull}>} Distinct WORKORDERID)

       

      I have tried also these versions below and they are wrong too:

       

      Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB'}> + < [ISOVERDUE]={'1'}, WORKORDERID={'=Len(Trim(CATEGORYNAME))=0'}>} Distinct WORKORDERID)

       

      Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB'}> + < [ISOVERDUE]={'1'}, CATEGORYNAME ={'-'}>} Distinct WORKORDERID)

       

      Count({$< [ISOVERDUE]={'1'}, CATEGORYNAME ={'AA','BB'}> + < [ISOVERDUE]={'1'}, CATEGORYNAME -={'*'}>} Distinct WORKORDERID)

       

      How to include null or blank values in KPI calculation?