Counting Effective records for a date

[#1]

Hi,

I am totally new to QlikView and have been requested to create a desktop including a count as below, but am totally stuck on this one.

I have a SQL view which contains details of customer status at different points in time. Each record contains 2 dates, a starting date and a redundant date. e.g. for one customer...

Customer Starting Redundant Status
A 01/02/2009 03/03/2009 Live
A 03/03/2009 30/04/2009 On-Hold
A 30/04/2009 31/07/2009 Live
A 31/07/2009 10/08/2009 Pending
A 10/08/2009 31/12/2099 Live

Note...the last record for each customer will always contain 31/12/2099 in the Redundant date.

I need to be able to count how many customers are live at any given date. The date will be entered (or selected) by the user.

I have managed to create a calendar using the two dates and Interval Match (although there are lots of duplicate dates), but I guess I now need to use Set Analysis somewhere to give what I need?

Any help would be very much appreciated.

[#2]

This formula should work =count( {$<status={Live}>} customer)

[#3]

Thanks for the quick reply.

I have put the formula in, but the figure does not seem correct. I think it is probably the way I have constructed the calendar, it is not picking up the correct records.

Any advise on how to select records where the date entered falls in the Start/Redundant range?

[#4]

I am not sure I understand the question...

Try creating a list box with Status. When you select a status the records will be filtered to show all relevant records.

Can you explain in more detail...

[Oleg_Troyansky]

yhou should add the Starting Date into your Set Analysis condition: Starting Date > Selected Date and Status = Live

Oleg

[#6]

In the example below:

Customer Starting Redundant Status
A 01/02/2009 03/03/2009 Live
A 04/03/2009 29/04/2009 On-Hold
A 30/04/2009 30/07/2009 Live
A 31/07/2009 10/08/2009 Pending
A 11/08/2009 31/12/2099 Live
B 01/01/2009 31/12/2099 Live
C 01/04/2009 31/12/2099 Live
D 01/08/2009 31/12/2099 Live
E 01/04/2009 18/04/2009 On-Hold
E 19/04/2009 31/12/2099 LIve
F 01/01/2009 31/12/2099 Pending

I would expect that if the user entered the date 01/03/2009 it should say 2 Live customers (A Live between 01/02/2009 and 03/03/2009 and B Live between 01/01/2009 and 31/12/2099).

If they entered the date 17/04/2009 it should say 2 Live customers (B Live between 01/01/2009 and 31/12/2099 and C Live between 01/04/2009 and 31/12/2099).

If they entered the date 01/05/2009 it should say 4 Live customers (A Live between 30/04/2009 and 30/07/2009, B Live between 01/01/2009 and 31/12/2099, C Live between 01/04/2009 and 31/12/2099 and E Live between 19/04/2009 and 31/12/2099 ).

i.e it should find the record where the date entered falls between the 2 dates and if the status is Live it should add to the count.

[johnw]

Carneyfm's solution, count({<status={Live}>} customer) should work, but depends on setting up your data in a particular way that you probably haven't done. Specifically, you would use an intervalmatch to join Dates in a calendar to the date range in your table. That would essentially map the status of each customer to the date, so that when you select a date, you're selecting the specific status for that date as well. The given expression can then be used to count customers that were live at any time in your selected date range. I've attached that approach.

[johnw · Accepted solution]

And here's a solution working without using intervalmatch. This might be necessary if, say, you had timestamps instead of dates. It would be completely impractical to autogenerate every possible timestamp just to do the intervalmatch, so you'd need another approach.

[#9]

The solution 'working without using intervalmatch' gave me a basis to work from and with some adjustments it now works fine.

My first time at using the forum and a great success.

Thanks to everyone for your help (especially John Witherspoon)!