Population between 2 dates

[#1]

I have a table of people with a date of Birth and a date of death if the person is deceased.

I want to be able to select a range of dates and have a counter of population alive during that period.

I guess the way QV works, I need to generate a table containing a person alive for each date that exists between the earliest DOB and today.

How can I script this?

My table people table has

person_id,

date_of_birth,

date_of_death

Thank you !

[#2]

One thing you can do is set a variable, make an input box, and make a chart like this:

sum(if(vDate>=[date_of_birth] And vDate<=[date_of_death], 1))

How you would get that in the script I would also like to know...

[rwunderlich]

sruault wrote:
I guess the way QV works, I need to generate a table containing a person alive for each date that exists between the earliest DOB and today.

Use IntervalMatch. First generate a Calendar table of Dates in the range. Then

intervalMatch (Date) LOAD date_of_birth, date_of_death RESIDENT mytable;

[#4]

Hi,

you can use set analisys, it should be something like this:

count({$<date_of_death={">$(max(date_field))"},date_of_birth={"<$(max(date_field))"}>} person_id)

hope this helps

Regards!!!

[#5]

I'm not really sure how intervalmatch works but if you have say 1,000,000 different people in the database and they live for 80 years each, won't that create an insane amount of data? I just tried intervalmatch on the data below and it turned 2 rows into 426... there's a calendar table I didn't post between the two tables. Did I do something wrong or is intervalmatch supposed to work like this?

Table1:
LOAD * INLINE
[
ID, Start, End
1, 1/1/2009, 1/1/2010
2, 1/2/2011, 3/2/2011
]
;

Final:
IntervalMatch (Date) LOAD [Start], [End] RESIDENT Table1;
JOIN LOAD * RESIDENT Table1;
DROP TABLE Table1;

[rwunderlich]

trent.jones wrote:I'm not really sure how intervalmatch works but if you have say 1,000,000 different people in the database and they live for 80 years each, won't that create an insane amount of data?

Yes, that's the dark side of IntervalMatch. It's an extremely useful feature but can become too large to work with. Gabriella's solution is probably better for this problem.

-Rob

[#7]

I found this thread below that I found pretty useful, maybe the OP will as well.

http://community.qlik.com/forums/t/23510.aspx

[#8]

Hi,

Thank you all for your attention and answers.

[Customers]:
LOAD [Customer ID],
if(isnull([End Date]), DayStart(now()), [End Date]) as [EndDate],
DayStart([Start Date]) as [StartDate]

FROM [../../QVD/Customer.qvd] (qvd)
;

I get the start date of my calendar :

[Min Date]:
LOAD min([StartDate]) as "StartDate"
RESIDENT [Customers];
LET minDate = FieldValue('StartDate', 1);

// Generate all dates

[Master Dates]:
LOAD date($(minDate) + recNo() - 1,'MM/DD/YY') as [Filter Date]
AUTOGENERATE(today() - $(minDate) + 1);

LEFT JOIN (Customers)
intervalMatch ([Filter Date]) LOAD StartDate, EndDate RESIDENT Customers;

When I execute the script, I get:

Error in expression:
')' expected
[Master Dates]:
LOAD date(5/25/2010 12:00:00 AM + recNo() - 1,'MM/DD/YY') as [Filter Date]
AUTOGENERATE(today() - 5/25/2010 12:00:00 AM + 1)

[rwunderlich]

You will need to use a different fieldname whane getting the mindate for your calendar.

[Min Date]:
LOAD min([StartDate]) as "MinStartDate"
RESIDENT [Customers];
LET minDate = FieldValue('MinStartDate', 1);

[#10]

Could you please share your qvw file.

I am same facing the same problem but not regarding the population but the data related with my work.