Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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);
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
Use Date and Date# functions:
Date(Date#(donation_date, 'M/D/YYYY hh:mm:ss tt'))
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.