Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Rachel,

The expression you have used to get the cases opened in last 90 days is right, but the 0 which you see (five cases, is not actually 0 it will be null which is represented as 0). Kindly confirm that. If its null which means there is no data for those records.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Rachel,

The expression you have used to get the cases opened in last 90 days is right, but the 0 which you see (five cases, is not actually 0 it will be null which is represented as 0). Kindly confirm that. If its null which means there is no data for those records.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
martinpohl
Partner - Master
Partner - Master

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

Anonymous
Not applicable
Author

Hi Martin,

Thank you for responding to me. Actually, the part you underlined is not the complete expression for AccountName. The advanced search condition for AccountName ends before the last "distinct AccountName", which is:


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


Best,

Rachel

Anonymous
Not applicable
Author

Hi Kaushik,

Thank you for reminding me of this! Yes, you are right. It seems that this should be the problem why I can't count those accounts. If these values are null then I can't count them. I modified the expression to count the accounts whose case counts don't equal to 0 and then it works!

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


Thank you for your great assistance!


Best,

Rachel