6 Replies Latest reply: Jul 13, 2017 12:56 PM by Rachel Zou RSS

    Nested Set Analysis - Count clients who didn't open any cases in the past 90 days

    Rachel Zou

      Hello Experts,

       

      I would like to count the clients who didn't open any cases in the past 3 months. Below is what I am using now:

       

      Count( {<AccountType={'Client'}, AccountName= {"=Count({<Date={ '>=$(=Date(Today()-89))<=$(=Today())' }, DateType={'CaseCreated'}>} [Case Number])=0 "}>} distinct AccountName)    

       

      The above expression gives me 0, while I know there are a number of accounts who haven't opened any cases in the past 90 days.

       

      Also, I have tested this a lot of times and tried to follow some solutions I found online, I wonder why even this simpler version doesn't work for me: Count({< AccountName ={" =Count([Case Number])=0 "} >} distinct AccountName)  This gives 0 while I know there exist clients whose haven't opened any cases yet.

       

      But this works: Count({<Date={'>=$(=Date(Today()-89))<=$(=Today())'}, DateType={"CaseCreated"}>} [Case Number])  This gives me the number of cases opened within the last 90 days.

       

      Not sure if it matters, but in case you wonder what DateType={'CaseCreated'} is, it is a customized date type in my calendar and defines what type of date it is when I choose a date range from my master calendar. I use it like this successfully in my other expressions: Count (distinct{<Year_Created={'>2014'}, DateType={"CaseCreated"}>}[Case Number])

       

      Any insights will be greatly appreciated.

       

      Best,

      Rachel

        • Re: Nested Set Analysis - Count clients who didn't open any cases in the past 90 days
          Kaushik Solanki

          Hi Rachel,

           

          First of all let us understand your expression and why it is not giving you result.

           

          Count( {<AccountType={'Client'}, AccountName= {"=Count({<Date={ '>=$(=Date(Today()-89))<=$(=Today())' }, DateType={'CaseCreated'}>} [Case Number])=0 "}>} distinct AccountName)   

           

          Syntactically your expression is right, you logically think, it wont find single accountname where case number is 0, because you dont have any such records. Let us understand this using your data.

           

          What I understand from the information you have given is, your table Seems to have below fields.

           

          TicketNumber,

          AccountName,

          Date,

          DateType,

          AccountType

           

          If Yes then you will observe that the data is coming to this table, only when a ticket is created.

          So you wont find any records which tells that this account has not created any ticket.

           

          So to get this insight, you will have to create those records in your data set.

          To do this, you need all the Accountname and date and create a master table from them and then you can link your actual transaction table to this master table. Something like below.

           

          Temp:

          Load Distinct AccountName from xyz;

          join(Temp)

          Load Distinct Date from mastercal;

           

          Master:

          LOad AccountName,Date,AccountName&Date as Key

          Resident Temp;

           

          Left join(Master)

          Load AccountName&Date as Key,

                   AccountTye,

                    ....

          From xyz;



          Hope it is clear.

           

          Regards,

          Kaushik Solanki

            • Re: Nested Set Analysis - Count clients who didn't open any cases in the past 90 days
              Rachel Zou

              Hi Kaushik,

               

              Thank you so much for your kind help. I hope I have understood your point fully before I reply. In case this is not what you meant, please bear with me.

               

              The context is, all the data I used were extracted from Salesforce and they contain all the cases opened and closed since 2015 and all the accounts who submitted these cases. Thus, it seems to me that I should already have these accounts and cases in my table, even if the accounts I am looking for didn't open any cases in the past 90 days.

               

              I currently get the table below showing correct numbers in Qlik:

                 

              Account NameCases Opened in 2015Cases Closed since 2015Cases Opened in last 90 days
              Company A220
              Company B110
              Company C550
              Company D16160
              Company E23220
              Company F111
              Company G101

               

              The expressions for the 4 columns are as below and they all work:

              if(AccountType='Client', AccountName)

              Count(distinct{<Year_Created={'>2014'}, DateType={'CaseCreated'}>}[Case Number])

              Count(distinct{<Year_Closed={'>2014'}, DateType={'CaseClosed'}, Status={'Closed'}>}[Case Number])

              Count({<Date={'>=$(=Date(Today()-89))<=$(=Today())'}, DateType={"CaseCreated"}>} [Case Number])

               

              So basically I am looking for an expression that could count the clients who opened 0 cases in the last 90 days. In this dummy table's case, that is 5. I wonder in this case, do I already have all the necessary data or should I do more joins?

               

              Again, thank you for your time!

               

              Best,

              Rachel

            • Re: Nested Set Analysis - Count clients who didn't open any cases in the past 90 days
              Martin Pohl

              I think

              Count( {<AccountType={'Client'}, AccountName= {"=Count({<Date={ '>=$(=Date(Today()-89))<=$(=Today())' }, DateType={'CaseCreated'}>} [Case Number])=0 "}>} distinct AccountName)

              the underlined part of your expression is something like AccountName = {5} (the count of Case Number)

               

              So why don't you use

              Count( {<AccountType={'Client'}, Date={ '>=$(=Date(Today()-89))<=$(=Today())' }, DateType={'CaseCreated'}>}  distinct AccountName)

              Regards