Forum Discussion
Resolver ISecond Gift Date
thanks MitchM , however, it only return the very last gift date, I need it to be able to show the first gift as well, so as in the example below:
Gift Table
Donor ID First donor date Second donor date Third donor date
1234 12/04/2018 15/05/2018
2345 06/04/2018
1111 07/06/2018 12/06/2018 21/08/2018
The end result would be:
Donor ID First donor date Second donor date
1234 12/04/2018 15/05/2018
2345 06/04/2018
1111 07/06/2018 12/06/2018
So not matter if the donor had donated 5 times, I would only want to see the first and second donor date if that makes sense?
Apologies if im confusing you, its almost there and what you have provided does kind of work, but I wouldnt know where to start to fix it.
It is no suprise that the filtering does not work. The ranking needs another value (In this case FY) to rank against. To do this you can create a new calc. column called FY:
FY = VAR FirstMonthofFY = 9 VAR DateYear = YEAR( GiftData2[Reformatted Date] ) VAR DateMonth = MONTH( GiftData2[Reformatted Date] ) RETURN IF( DateMonth < FirstMonthofFY, (DateYear - 1) & "/" & DateYear, DateYear & "/" & (DateYear + 1) )I am not sure when your FY starts, so just change the first variable (FirstMonthofFY) in the formula above to adjust the FY range. Once this is done a slight tweak to the Rank formula (below) should finish it off. The two dontation measures do not need to be adjusted.
Gift_Date_Rank = RANKX( FILTER( GiftData2, GiftData2[Donor ID] = EARLIER( GiftData2[Donor ID] ) && GiftData2[FY] = EARLIER( GiftData2[FY] ) ), GiftData2[Reformatted Date], , ASC, Dense )Another question and hopefully the last - once I have the campaign id's and how when the donors gave their 1st and 2nd gift, how would I go about doing a count of how many times the campaign id has appeared, so for example
In 2019 for Jan - there were 200 donors that donated a gift on campaign id "2", out of the 200 donors that gave on campaign id "2", 100 donors gave their second gift on campaign id "3", and 50 donors gave on campaign "4" and 50 donors never gave a second gift..
So its trying to identify out of all the donors that gave their first gift and to which campaign gave again and if it was to a different campaign code or not.
Hope that makes sense
TIA
Resolver IMitchM- Your calculation works, however one slight problem( table below:) is that, as some donors may have given gifts in 1998 so it is taking this as the first gift, so when I apply a filter for FY 18/19, it is not showing the first gift and second gift correctly also to make things more complicated, the donor could have given 2 gifts on the same day but at different times.
Is there anyway around this?
| Gift ID | Gift Date | Gift Amount | Gift Code | Gift Type | Post Date | Donor ID |
| 1 | 06/04/1998 | £10.00 | Standing order | Cash | 12/04/1998 | 1010 |
| 2 | 08/05/1999 | £10.00 | Standing order | Cash | 09/05/1999 | 1010 |
| 3 | 06/06/2000 | £10.00 | Standing order | Cash | 12/06/2000 | 1010 |
| 4 | 06/07/2004 | £10.00 | Standing order | Cash | 14/07/2004 | 1010 |
| 5 | 06/08/2005 | £10.00 | Standing order | Cash | 08/08/2005 | 1010 |
| 6 | 06/09/2018 | £10.00 | Standing order | Cash | 09/09/2018 | 1010 |
| 7 | 08/10/2018 | £10.00 | Standing order | Cash | 21/10/2018 | 1010 |
| 8 | 06/05/2019 | £10.00 | Standing order | Cash | 12/05/2018 | 1010 |
| 9 | 22/08/2009 | £100.00 | Direct donation | Cash | 01/09/2009 | 202 |
| 10 | 12/06/2018 | £235.30 | InMem | Cash | 13/06/2018 | 202 |
| 11 | 06/05/2019 | £10.00 | Standing order | Cash | 22/05/2019 | 300 |
| 12 | 06/05/2019 | £100.00 | Direct donation | Cash | 22/05/2019 | 300 |
TIA
Resolver IIIt is no suprise that the filtering does not work. The ranking needs another value (In this case FY) to rank against. To do this you can create a new calc. column called FY:
FY =
VAR FirstMonthofFY = 9
VAR DateYear = YEAR( GiftData2[Reformatted Date] )
VAR DateMonth = MONTH( GiftData2[Reformatted Date] )
RETURN
IF(
DateMonth < FirstMonthofFY,
(DateYear - 1) & "/" & DateYear,
DateYear & "/" & (DateYear + 1)
)I am not sure when your FY starts, so just change the first variable (FirstMonthofFY) in the formula above to adjust the FY range. Once this is done a slight tweak to the Rank formula (below) should finish it off. The two dontation measures do not need to be adjusted.
Gift_Date_Rank =
RANKX(
FILTER(
GiftData2,
GiftData2[Donor ID] = EARLIER( GiftData2[Donor ID] ) &&
GiftData2[FY] = EARLIER( GiftData2[FY] )
),
GiftData2[Reformatted Date],
,
ASC,
Dense
)- UK_User123456
holy moly, this would be easier in excel, but im appreciative of all your help
- UK_User123456
MitchM- many thanks for helping with this, it has worked wonders, however, if I wanted to show the campaign(s) against these donations, would I need to follow the same path? I would like to show the campaign(s) for both the first donation and second donation?
Thanks
- MitchM
No worries! Yes, if you wanted to add an extra dimension to the ranking you would need to add it to the Rank calculated column in a similar way I added FY. Just rember that for every subcategory you add to the rank equation is further slicing down your data. You could always add a second rank column (and additional measures) if you want to rank by another subcategory.
- UK_User123456
Another question and hopefully the last - once I have the campaign id's and how when the donors gave their 1st and 2nd gift, how would I go about doing a count of how many times the campaign id has appeared, so for example
In 2019 for Jan - there were 200 donors that donated a gift on campaign id "2", out of the 200 donors that gave on campaign id "2", 100 donors gave their second gift on campaign id "3", and 50 donors gave on campaign "4" and 50 donors never gave a second gift..
So its trying to identify out of all the donors that gave their first gift and to which campaign gave again and if it was to a different campaign code or not.
Hope that makes sense
TIA