If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
Hello Qlik Experts,
I have two (maybe three if necessary) tables I'm looking at for this one, Donors and Gifts:
Donor:
DonorID,
AssignedFlag (this is a Y or N)
Gift:
GiftID,
GiftDate,
DonorID
(An optional third is a Gift Calendar😞
GiftDate,
Year(GiftDate) as GiftYear,
Month(GiftDate) as GiftMonth
I need to Count the set of Donors who are Assigned that gave a gift in 2015, and compare that to Donors that were assigned that gave a gift in 2015 AND in 2016.
Calc 1 = Donors with a 'Y' in AssignedFlag AND who have a Gift Date in 2015
Currently I'm using this calculation:
Count({$<[Assigned Flag]={'Y'},[Gift Year]={$(=Year(Today())-2)}>}Distinct [Donor ID])
This seems to be working correctly, but would appreciate feedback if I'm missing something...
Calc 2 = Donor with a 'Y' in AssignedFlag AND who have a Gift Date in 2015 AND who have a Gift Date in 2016
This is the Calculation I'm having trouble getting correct and am looking for help on.
Basically we're trying to calculate who gave a gift 2 years ago and of those donors, who gave again in 2016.
Thanks in advance for the help!
Calc 1 = Donors with a 'Y' in AssignedFlag AND who have a Gift Date in 2015
=COUNT({<DonorID = p({1<AssignedFlag = {'Y'}, GiftYear = {'$(=Year(Today())-2)'}>}DonorID)>}DISTINCT DonorID)
Calc 2 = Donor with a 'Y' in AssignedFlag AND who have a Gift Date in 2015 AND who have a Gift Date in 2016
=COUNT({<DonorID = p({1<AssignedFlag = {'Y'}, GiftYear = {'$(=Year(Today())-2)'}>}DonorID) * p({1<AssignedFlag = {'Y'}, GiftYear = {'$(=Year(Today())-1)'}>}DonorID)>}DISTINCT DonorID)
To make things a simple as possible in the app I would create these flags in your load script.
2015Donated Flag and 2016DonatedFlag
Then in the app you don't have worry about anything but checking these 2 flags. No complicated Set Analysis.
Definitely willing to take that solution for a spin. Then a count with AND conditions will work.
However, I'm concerned this isn't an expandable solution. What happens next year when this rolls around? I guess I should've phrased this question as:
What Assigned Donors gave two years ago, then also gave again last year?
Calculate variables in your script and use them instead of hard coding years.
Calc 1 = Donors with a 'Y' in AssignedFlag AND who have a Gift Date in 2015
=COUNT({<DonorID = p({1<AssignedFlag = {'Y'}, GiftYear = {'$(=Year(Today())-2)'}>}DonorID)>}DISTINCT DonorID)
Calc 2 = Donor with a 'Y' in AssignedFlag AND who have a Gift Date in 2015 AND who have a Gift Date in 2016
=COUNT({<DonorID = p({1<AssignedFlag = {'Y'}, GiftYear = {'$(=Year(Today())-2)'}>}DonorID) * p({1<AssignedFlag = {'Y'}, GiftYear = {'$(=Year(Today())-1)'}>}DonorID)>}DISTINCT DonorID)
Thank you Manish, this seems to be working!
Can you explain the use of "p" and the "1" before each set?
Check below link.
Set modifiers with implicit field value definitions ‒ QlikView
Try to understand the use of P() and E() from this link.
This is also called as Indirect Set Analysis.
DonorID = P(DonorID)
Mean Possible DonorID
DonorID = p({1<AssignedFlag = {'Y'}, GiftYear = {'$(=Year(Today())-2)'}>}DonorID)
Here Possible DonorID which are having AssignedFlag = Y and GiftYear 2 years back compare to today's year
1 is used to ignore the filters users will do in application, so that the end result would not get affected.
Regards,
MK
mrkachhiaimp Thanks for the links on this. This helped my understanding quite a bit after seeing it applied to this solution, so thanks!
As a follow up question, now that I have these 2 list of Donors with the qualifications, I'm trying to figure out what the Money lost is from 2015 to 2016. The difference between Calc 1 and Calc 2 is what we're calling "lapsed donors". If I subtract Calc 2 from Calc 1 I get the list of Donors who are assigned that gave in 2015 but NOT 2016. I'd like to find out how much money we lost assuming they would've given the same amount.
Does this make sense? How would I go about calculating this?
Provide sample data here.. I will try to give you expressions for that.
Sure.
Donors
DonorID | AssignedFlag | Total2015 | Total2016 |
---|---|---|---|
001 | Y | 5000 | 10000 |
002 | Y | 10000 | 0 |
003 | N | 25000 | 5000 |
004 | N | 5000 | 0 |
005 | Y | 2000 | 0 |
006 | Y | 20000 | 10000 |
007 | Y | 0 | 5000 |
Gifts
GiftID | DonorID | GiftDate | GiftAmount |
---|---|---|---|
101 | 001 | 1/1/2015 | 5000 |
102 | 001 | 1/1/2016 | 10000 |
103 | 002 | 2/1/2015 | 10000 |
104 | 003 | 3/1/2015 | 25000 |
105 | 003 | 3/1/2016 | 5000 |
106 | 004 | 4/1/2015 | 5000 |
107 | 005 | 5/1/2016 | 2000 |
108 | 006 | 6/1/2015 | 20000 |
109 | 006 | 6/1/2016 | 10000 |
110 | 007 | 7/1/2016 | 5000 |
With this data, the donors we would "lose" money from are 002 and 005 because they gave in 2015 but not in 2016 (Notice Donor 004 also gave in 2015, not in 2016, but isn't Assigned). The total money lost from these two Donors, if they would've given the same amount HAD they given, would be $12,000 ($2,000 for Donor 005 + $10,000 from Donor 002).
Thanks for taking a look!