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: 
mjirges
Partner - Contributor II
Partner - Contributor II

How do I count the number of clients that have multiple service within 60 days of a specific service?

I am trying to count the number of clients that have more then 1 service after a particular service (Comprehensive Assessment).

In order to be counted, more then 1 service must be present within 60 days of the Comprehensive.

All relevant data is in the same table/fields. The "Comprehensive" Service and subsequent services are all in the same place (example in screenshot). 

 

In the example, Clients 1, 2 and 5 would be counted. 

1 has four services that count

2 has three services that count

3 does not have a Comprehensive, therefore wouldn't count

4 only has one service that counts, therefore wouldn't be included

5 has two services after the Comprehensive, therefore would count

6 doesn't have any services in the timeframe, therefore wouldn't count.

 

What kind of expression would I use? I don't have access to the script, so I can only write this in an expression. 

Labels (2)
1 Reply
pravinboniface
Creator II
Creator II

Not the most elegant solution, but if you can sort the table by client_id, Service_Name and Service_Date, then you can use something like this.

if(below(TOTAL client_id)=client_id and
   Service_Name='Comprehensive' and
   interval(below(TOTAL Service_Date)- Service_Date,'d') < 60 and
   interval(below(TOTAL Service_Date)- Service_Date,'d') >=0, 1,0)