Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
jlg
Contributor II
Contributor II

KPI to show Number of Lost Customers

Hi. 

I have a data set where I am trying to show lost customers. 

I have created a lost customer Indicator that shows a 1 for a lost customer, and a 0 for non-lost customers, based in whether a customer had more than 2k of sales 2-4 months ago, but 0 sales in the last 2 months. All seems to work well in a table as below:

jlg_0-1624021108097.png

 

Where I am stuck is how to display this as a KPI on a dashboard. I want a count of the customers that have a Lost Customer Indicator of 1, but can't work out how to do this.

 

Any help appreciated

Thanks

 

 

1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

Your KPI metric expression will be something like this:

=count({<[Delivery Name]={"=sum({$<[Sales Date]={"">=$(=AddMonths(Today(),-4))<=$(=AddMonths(Today(),-2))""}>} [Sales])>2000 and
sum({$<[Sales Date]={"">=$(=AddMonths(Today(),-2))""}>} [Sales])=0"}>}Distinct [Delivery Name])

[Delivery Name], [Sales Date], and [Sales] will need to be changed to the appropriate field names in your data model.  And if your Lost Customer Inidicator is different than what I used, it will need to be changed, but concept will remain the same.

View solution in original post

1 Reply
GaryGiles
Specialist
Specialist

Your KPI metric expression will be something like this:

=count({<[Delivery Name]={"=sum({$<[Sales Date]={"">=$(=AddMonths(Today(),-4))<=$(=AddMonths(Today(),-2))""}>} [Sales])>2000 and
sum({$<[Sales Date]={"">=$(=AddMonths(Today(),-2))""}>} [Sales])=0"}>}Distinct [Delivery Name])

[Delivery Name], [Sales Date], and [Sales] will need to be changed to the appropriate field names in your data model.  And if your Lost Customer Inidicator is different than what I used, it will need to be changed, but concept will remain the same.