topic Re: Getting value for a dynamic date range using Set Analysis in QlikView

Getting value for a dynamic date range using Set Analysis

Tue, 09 Sep 2014 19:46:35 GMT

Hi,

I am new to Set Analysis, appreciate your inputs on the following scenario. I have a table as follows:

Service Number	Service Date	Customer ID
10001	9/1/2014	1
10002	9/2/2014	1
10003	9/12/2014	1
10004	9/2/2014	2
10005	9/5/2014	2
10006	9/3/2014	3
10007	9/12/2014	3
10008	9/4/2014	4
10009	9/5/2014	4
10010	9/6/2014	5
10011	9/21/2014	5

Problem: I need to find the distinct number of customers who availed a service for the first time for a date range (say in this case from 9/1/2014 to 9/5/2014) and then came back within 5 days of availing the service. In the above example the count should consider only the customers 1, 2 and 4.

Thank you!

Re: Getting value for a dynamic date range using Set Analysis

Wed, 10 Sep 2014 03:35:30 GMT

Find out the below app attached and let us know the approach sutis the requirement

Re: Getting value for a dynamic date range using Set Analysis

Wed, 10 Sep 2014 03:41:32 GMT

small corerction

istead of using "sum" in the expression use "count "

you would get desired result

Re: Getting value for a dynamic date range using Set Analysis

Wed, 10 Sep 2014 05:12:33 GMT

Hi,

Thanks for your inputs, unfortunately it is not solving the problem i described. This is giving all the customers count for the date range 9/1/2014 to 9/5/2014. I want the count of only those customers who came back within 5 days of availing the first service. In the example i have given the count should consider only the customers 1,2 and 4 as they have come back within 5 days of availing the first service.

Re: Getting value for a dynamic date range using Set Analysis

Wed, 10 Sep 2014 05:33:01 GMT

try for subroutine

if you are not aware of it try to find it out in the community,it may help you

Re: Re: Getting value for a dynamic date range using Set Analysis

MayilVahanan — Wed, 10 Sep 2014 05:56:33 GMT

PFA

hope it helps

Re: Getting value for a dynamic date range using Set Analysis

Thu, 11 Sep 2014 10:03:40 GMT

Thanks Mayil!!!!! This solved my problem.

However I am still curious to know if this can be done through set analysis and not add anything to the scripts.

Thank You!!!!