8 Replies Latest reply: Jul 26, 2017 2:13 PM by P B RSS

    KPI Value - Count of all FamilyIDs that satisfy multiple conditions

    P B

      Hi,

       

      I've been using QlikSense for a month now. I'm trying to build a KPI which is a division of 2 counts. The numerator is slightly complicated- the count of all distinct family IDs whose total sum of Field1 and Field2 is greater than or equal to 50k, who belong to customer 'A' and have been Assisted. The denominator is the count of all families that belong to customer 'A' and whose total sum of Field1 and Field 2 is greater than or equal to 50k. I've tried a few things but doesn't seem to work. The code I wrote is as follows:

       

      count({<NEW_FAMILYID = {"=sum(Field1)+sum(Field2)>=50000"}, CUSTOMER={'A'}, ASSISTED_CLIENT={'1'} >} distinct NEW_FAMILYID)

      /

      count({<NEW_FAMILYID = {"=sum(Field1)+sum(Field2)>=50000"}, CUSTOMER={'A'}>} distinct NEW_FAMILYID)

       

      Another thing I tried:

      count({<ASSISTED_CLIENT = {'1'},

      CUSTOMER = {'A'}>}

      distinct If(Aggr(sum(Field1) + Sum(Field2),NEW_FAMILYID)>='50000', NEW_FAMILYID))

      /

      COUNT({<CUSTOMER = {'A}>} DISTINCT If(Aggr(sum(Field1) + Sum(Field2),NEW_FAMILYID)>='50000', NEW_FAMILYID))