topic Re: How to Determine Timeline in QlikView

How to Determine Timeline

Wed, 19 Mar 2014 22:28:11 GMT

Hi,

I have a list of customers with Contract Start and Contract End Dates. I like to find out:

1. How many customers's contract end next day?

2. How many customers' contract end within Seven dayas? I want to select the date value as input, and QlikView must list those customers that fall outside the selected date.

3. How many customers' contract end with March 31, 2014?

I have appended the sample data

Customer	Contract Start	Contract End
A	8/9/2013	8/8/2014
B	7/15/2011	7/14/2014
C	9/17/2013	9/16/2016
D	3/31/2013	3/31/2014
E	4/19/2011	4/18/2014
F	5/28/2011	5/27/2014
G	6/16/2011	6/15/2014
H	10/18/2011	10/17/2014
I	5/12/2011	5/11/2014

The output must list customers who fall outside the selected date. Any suggestions are welcome

Thanks,

Re: How to Determine Timeline

Wed, 19 Mar 2014 22:37:14 GMT

With the intervalmatch function you can answer 1 and 3 and probably 2 too.

Google on how to use and im sure you find the technical designblog and other helpful articles

hope it helps!

Re: How to Determine Timeline

swuehl — Wed, 19 Mar 2014 22:58:03 GMT

In principle, you should be able to use a simple if() condition or set analysis, like for a)

=count(if([Contract End]=today()+1, Customer))

=count( {<[Contract End]= {'$(=Date(today()+1))'}>} Customer)

For b) and c)

=count( {<[Contract End]= {">=$(=Date(vSelectedDate))<=$(=Date(vSelectedDate+7))"}>} Customer)

=count( {<[Contract End] = {'3/31/2014'}>} Customer)

The output must list customers who fall outside the selected date. Any suggestions are welcome

Thanks,

If you want to list the customers that doesn't match above, use the -= operator, like

=count( {<[Contract End] -= {'3/31/2014'}>} Customer)

Re: How to Determine Timeline

Wed, 19 Mar 2014 22:58:24 GMT

You can add a third field with the difference between the two: "days since end contract" and drive the selection with a variable.