Hello,
I am trying to compare year to date contributions in several funds for this fiscal year against year to date contributions in the same funds for last fiscal year. I believe that I must use a Campaign Fiscal Current Year Offset filter to compare the two years, and a Contribution Date Fiscal Year To Date Flag filter to only compare YTD gifts.
When I add the Campaign Fiscal Current Year Offset filter the numbers look correct. However, when I add the Contribution Date Fiscal Year To Date Flag filter, the numbers are much different than what I am seeing if I run the Campaign Giving Comparison report with similar parameters.
I appreciate any advice you can give on the correct use of filters.
Thank you,
John
I think the problem may stem from the fact that many of these contributions may have come in before the fiscal year started. A gift might be intended for FY24, but came in during FY23. I am currently looking into solutions and appreciate any advice.
This might be a 'Transaction Date' (this thing happened on January 1st, 2023) vs 'Contribution Date' (this thing was for December 31st, 2022) thing. I would start by simplifying filters. In the Contributions Cube, set up filters for this Contribution Date Year and last (2023 vs 2022), and use 'Contribution Date' > 'Calendar Number Day of Year' to go from Jan 1st through yesterday (whatever number that was). Then compare those numbers to your first widget.
You also could use the Finance Cube and pull in 'Transaction Date' as well as 'Contribution Date' and look at the same things. Getting a handle on why Analytics is displaying the numbers as it is will help figure out how to properly mirror that standard report.
Hello Nathanael, .
Thank you for your suggestions. We enter Contribution Date as the day the contribution was received by Finance. These are often before the start of the fiscal year.
I am thinking an open ended filter where the start date is 00/00/0000 and the end date is yesterday would work, but I haven't begun to attempt to set that up yet.
To restate that to make sure I understand- you could (conceivably) receive a contribution today that was intended for 2024. In your setup, that 'Contribution Date' would be today, right? Would the Campaign you use be 2023 or 2024?
If a check came in the mail today, it might not make it to my desk until tomorrow, but the Contribution Date would be 8/29/2023.
If a check came in May 19, 2023, but the donor intended it for FY24, the Contribution Date would be 5/19/2023, but it would apply to FY24, and I would want to see it under the FY24 YTD numbers.
I think that might be the reason why your numbers are different. Your 'Campaign Fiscal Year' is based on when the money is for (2024), but the Contribution Date is based on when the money came in (2023). Part of the trick with Analytics reports is crafting things so that each widget is asking the same questions as the standard reports.
I created a custom filter which has gotten the numbers correct.
If you or anyone else has any input on how to improve this formula, I would greatly appreciate it. I don't fully understand the syntax of these queries yet.
{ "last": { "count": 9999, "offset": 365 }, "custom": true}
Thank you!
Last means go into the past. Offset tells it where to start. Count tells it how many days to go (into the past since you have it as last).
Knowing how the Advanced filter works allows you to do things like getting month to date numbers. We want a dashboard that gives the total month numbers as well as MTD.
In this filter (on the Widget level), I only want this month's performances up to yesterday (we have daily performances). This filter allows me to get that. I have it go back 40 days in the past (offset = -40) and then count the next 40 dates.
This widget will give the proper MTD numbers for August until September 9th (which gives me about a week in case someone deleted the emailed version, and I can still send them a correct dashboard email and not have to make any changes).
On the formula level, I have a filtered value formula that only gives me performances that are in August.
The combination of filters on 2 different levels results in a correct month to date calculation. I then copy the dashboard and change the filters to the next month (September).
Thank you for that. This is very useful info. Do you know of any resource that has similar info? So far, I have only advanced through trial and error and forum posts.