Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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 Name | Cases Opened in 2015 | Cases Closed since 2015 | Cases Opened in last 90 days |
Company A | 2 | 2 | 0 |
Company B | 1 | 1 | 0 |
Company C | 5 | 5 | 0 |
Company D | 16 | 16 | 0 |
Company E | 23 | 22 | 0 |
Company F | 1 | 1 | 1 |
Company G | 1 | 0 | 1 |
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
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
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
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
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