topic Re: Help needed with expression for data that falls within 2 date ranges in QlikView

Help needed with expression for data that falls within 2 date ranges

Fri, 05 Oct 2012 11:53:50 GMT

Hello,

I want to split our supporters into active and inactive groups - Active is defined as someone who has made a donation in the past 6 years, inactive as one who has not. What expression do I need to use to get to this?

Many thanks

Lindsay

Re: Help needed with expression for data that falls within 2 date ranges

jagan — Fri, 05 Oct 2012 12:31:13 GMT

Hi,

Try below expressions

Active:

=Count({<Date={'>=$(=AddYears(Today(), -6))'}>} Supporter)

Inactive:

=Count(DISTINCT Supporter) -

Count({<Date={'>=$(=AddYears(Today(), -6))'}>} DISTINCT Supporter)

Hope this helps you.

Regards,

jagan.

Re: Help needed with expression for data that falls within 2 date ranges

Fri, 05 Oct 2012 12:45:37 GMT

To get the list of supporters try to use inner join, i.e. for active supporters:

People:
LOAD * Inline [
uid, name
1, John
2, Peter];

Donations:
LOAD * Inline [
uid, donation_date
1, 2012-01-01
2, 2001-01-01];

ActiveSupporters:
LOAD uid, name as name_active
Resident People;
Inner Join
LOAD uid
Resident Donations
Where Date(donation_date) > AddYears(Today(), -6);

Re: Help needed with expression for data that falls within 2 date ranges

Fri, 05 Oct 2012 13:37:56 GMT

Thanks for the prompt reply. I couldn't get either suggestions to work.

The DATEDUE field (used against payments) formats dates as 4/10/200 12:00:00 AM - would that affect the expression above?

It's been a year since I looked at expression writing - bit rusty!

Thanks so much,

Lindsay

Re: Help needed with expression for data that falls within 2 date ranges

Fri, 05 Oct 2012 13:49:23 GMT

Use Date and Date# functions:

Date(Date#(donation_date, 'M/D/YYYY hh:mm:ss tt'))

Re: Help needed with expression for data that falls within 2 date ranges

jagan — Fri, 05 Oct 2012 14:46:46 GMT

Hi,

While loading arrive num field for date by using

Num(DateField) AS DateNum

Now use this DateNum field in set analysis.

Try below expressions

Active:

=Count({<DateNum={'>=$(=Num(AddYears(Today(), -6)))'}>} Supporter)

Inactive:

=Count(DISTINCT Supporter) -

Count({<DateNum={'>=$(=Num(AddYears(Today(), -6)))'}>} DISTINCT Supporter)

Hope this helps you.

Regards,

jagan.