2 Replies Latest reply: Jun 28, 2017 12:11 PM by Reiko Hayami RSS

    Count based on Aging and other conditions

    Reiko Hayami
    Reiko Hayami Jun 28, 2017 11:57 AM

      I have the table that shows count of Active cases (called Incidents).  The age of an incident is calculated by Today - Incident Date.  I want them to ignore time selections, is why the {1}.

       

      Total is correct as below:

      Sum({1}ActiveCaseCnt)

       

      Less than 15 days old is correct as below:

      COUNT({1<StateValue={"Active"}>}if((today()-IncidentDate<15),1))

       

      Other ranges would not be correct because the below would count Less than 25 days but also Less than 15 days.  I know how to do this in Excel but I can't seem to get it right in Qlik Sense.

      COUNT({1<StateValue={"Active"}>}if((today()-IncidentDate<25),1))

       

      Visualization currently looks like this and they obviously don't add up to the total... What's the exact expression to set the range for Less than 25 days old but more than 15 days old, and so on?  I need to get this published for Monday or I will be stuck manipulating row data on Sunday.... Thanks for your help.