topic Population between 2 dates in QlikView

Population between 2 dates

Thu, 03 Mar 2011 18:55:50 GMT

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 !

Population between 2 dates

Thu, 03 Mar 2011 19:13:19 GMT

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...

Population between 2 dates

rwunderlich — Thu, 03 Mar 2011 19:19:40 GMT


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;

Population between 2 dates

Thu, 03 Mar 2011 19:23:45 GMT

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!!!

Population between 2 dates

Thu, 03 Mar 2011 20:00:08 GMT

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;

Population between 2 dates

rwunderlich — Thu, 03 Mar 2011 20:22:47 GMT


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

Population between 2 dates

Thu, 03 Mar 2011 20:26:29 GMT

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

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

Population between 2 dates

Fri, 04 Mar 2011 15:02:19 GMT

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)

Population between 2 dates

rwunderlich — Fri, 04 Mar 2011 20:04:06 GMT

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);

Re: Population between 2 dates

Fri, 15 Feb 2013 10:17:44 GMT

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.

Re: Population between 2 dates

Fri, 15 Feb 2013 10:27:46 GMT

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.