[nak]

I am trying to create a chart that shows our gifts received in each fiscal year which is July to June, but only include gifts to the current date.

I figured out how to do the fiscal year thing, but now I need to add something to include gifts received in previous years only until today's date.

So for this year, it would have all gifts received from 7/1/07- the day the report is run (say 3/1/08). For last year it would include from 7/1/06-3/1/07, and then for the year before from 7/05-3/06 etc.

I don't know if I am over thinking this, but at this point, I'm not even sure I understand what I am trying to do.

Any ideas?

[DrewAllen]

I actually was just working on this same problem. There are many ways to approach this, but here is what I came up with:

DateAdd(
    'yyyy', 
    DateDiff(
        'yyyy', 
        DateAdd('q', 2, {Gf.Gf_Date}), 
        Today), 
    {Gf.Gf_Date}) 
< Today 

It's a little bit convoluted, but it should work unless I switched some of the dates around.

Drew

[Melissa Siobhan]

Drew,

Can you be a bit more specific or even post a sample of the crystal report you used this formula in?

What I want is a report grouped by appeal category (appeal and appeal category are required fields on my system) summarizing giving for this FY to date and the past 3 fiscal years through the same date.

How do I plug this field into a report and use it? Sorry for the very novice crystal question.

[Doug Creek]

BIG NOTE: I had to fool the forums into letting me attach this file. Its actually a zip file. Save it, and change the extension to .zip and it should unpack like its supposed to. Peter, can we please get the limit for zip files increased?

I've attached a crystal report that I built to do this... I actually cheat and select all the data between the start date (the previous year) and the end date (the end of whatever period we're looking at), but only DISPLAY what I want to be displayed... I did it first as a bunch of sub reports, but it was so slow it was useless. This is a little convoluted to look at it, but it works, and isn't actually all that bad to maintain... Just takes some getting used to...

This is in CR XI R1, so 8.5 users won't be able to open it, sorry

Note: this is run off the backend using a bunch of stuff that we're probably the only ones that do it this way, so its not a drag and drop solution...but I think you should be able to open it since I saved the data with the report. I'm also using some stored functions (because I use crystal reports server), so I don't know if you can look at all of the formulas. If you have questions about any of them, let me know... Also, its REALLY slow to open on my desktop, I think its because of the stored data... I can usually open and run it faster then it even just opens on my desktop...

Legal Disclaimer: This report may or may not contain real data about the University of Alaska Foundation, and should not be treated as a "real" report in that sense. <-- Sorry, I was told I had to do this if I released one of my reports.

Doug

[DrewAllen]

Quote:

Originally Posted by Melissa Siobhan

Can you be a bit more specific or even post a sample of the crystal report you used this formula in?

Well, I was doing this in TIE, but the formula should translate. This produces a Boolean that tells you whether the current gift is in the YTD for the corresponding FY. You may want to create this as a separate formula if you're planning to use this calculation a lot, or you can simply include it as part of a longer formula. I think that I made an error in the original formula. Here is the corrected version.

DateAdd(
    'yyyy', 
    DateDiff(
        'yyyy', 
        DateAdd('q', 2, {Gf.Gf_Date}), 
        DateAdd('q', 2, Today)), 
    {Gf.Gf_Date}) 
< Today 

Basically what it is doing is converting both the gift date and the current date to a fiscal date and then calculating the difference between the two in years and then adding that difference to the actual gift date. So for this year, it will convert all dates to fall between 7/1/2007 and 6/30/2008 while keeping the actual month and day the same. If that resultant date is less than today (gift dates are recorded as midnight) then it's in the FYTD.

Drew

[stancliff]

This question made me look back and learn from reports that Blackbaud consultants wrote prior to us going live.

The two examples I have are using reports from the back end. But I think the first example, which is basicaly the same as Drew's, could be used for reports that are hung in RE.

Example #1

if {DateDim.ActualDate} in dateadd("yyyy",-1,{?Start Date}) to 
dateadd("yyyy",-1,{?End Date}) then {GiftFact.Amount}

Here's how Crystal Reports XI: The Complete Reference explains the DateAdd function.
DateAdd (s, n, dt)
s - string value indicating interval type (days, weeks, and so forth) Note: in my example, yyyy means year.
n - a number value indicating number of intervals to add to the source date-time. This can be a negative or postive number.
dt - a date or date-time value indicating the source date-time to add intervals to.

Example #2
If you are writing the report against the back end, you could always prompt the user to enter two or more pairs of start/end dates. We have a gift range report that displays the number and total sum of gifts by gift range for the current and past two fiscal years. The user is prompted to enter:
Current FY date range: [start,end]
Last FY date range: [start, end]
Two FY ago date range: [start, end]

Doing something like this offers great flexability. It allows the user to compare this fy to fy minus 3 to fy minus 10 if they really wanted. Or, they can compare this fy to date versus the complete fys of fy-1 and fy-2. The downfalls? The more parameters the user has to manually enter, the greater room for error. The report writer could always display the parameters on the report for insurance, or create an alert to be thrown if an unexpected value is entered.

[jas.]

I don't know if this will help, but I did something like stancliff's example #2 in my monthly summaries that I was working on in a big long thread last year around this time.

I have a parameter field called ?Pm-This YTD that asks for the YTD date when the report is run.

Then I have a formula called @ytd-last that looks like this:

dateadd ("d", -365, {?Pm-This YTD})

I don't remember why I subtracted 365 days instead of one year.

Anyway, then I have another formula called @FY07-ytd-amount that outputs the gift amount if it's in that date range or else ignores it:

if {Gf.Gf_Date} in date (2006,07,01) to {@ytd-last}
   then {GfCmps_1.GfCmps_1_Amount}
else 0

Then I summarize based on various groups in the report.

I know it's not as automatic as Drew's formula, but it works--the numbers add up like they're supposed to.

[DrewAllen]

Quote:

Originally Posted by jas.

I don't remember why I subtracted 365 days instead of one year.

Quote:

I know it's not as automatic as Drew's formula, but it works--the numbers add up like they're supposed to.

Hate to tell you this, but it doesn't work for leap years. 29 Feb 2008 will be compared to 1 Mar 2007 and, even worse, 30 June 2008 will be compared to 1 July 2007. Depending on the rest of your formulas, that could be either a range of a year and a day or just one day. DateAdd() and DateDiff() automatically account for leap years, and will line up as expected if you use years, quarters, or months, but not if you use days.

Drew

[jas.]

Quote:

Originally Posted by DrewAllen

Hate to tell you this, but it doesn't work for leap years. 29 Feb 2008 will be compared to 1 Mar 2007 and, even worse, 30 June 2008 will be compared to 1 July 2007. Depending on the rest of your formulas, that could be either a range of a year and a day or just one day. DateAdd() and DateDiff() automatically account for leap years, and will line up as expected if you use years, quarters, or months, but not if you use days.

Drew

hm... good thing this came up in early march 2008, eh? I'ma go ahead and fix that now. I wonder why I didn't use year, though. It seems kooky enough that I must have had a reason at the time (edited: or, I thought I did).

[nak]

Thank you everyone!

I now have the most kick-ass report/charts possible for comparing gift & donor information across fiscal years & quarters!


Drew,
I'm not sure I completely understand your formula (and it took me most of the day to understand what I do get), but it works like a charm. Thank you soooooooooo much!

And is there any chance you teach Advanced Crystal classes? Because I have to say that none of the classes I have seen come even close to being able to answer most of my questions.

[stancliff]

nak, do you have the book Crystal Reports XI: The Complete Reference by Peck? It is my Crystal bible. I've pretty much self taught myself in the past nine months using that book.

[DrewAllen]

Quote:

Originally Posted by nak

And is there any chance you teach Advanced Crystal classes? Because I have to say that none of the classes I have seen come even close to being able to answer most of my questions.

I'm not really a good teacher. I grok a lot of this stuff, so it's hard for me to see why other people are having problems with it, which makes it hard to explain it in terms they will understand.

Drew

[Melissa Siobhan]

I feel like such an idiot for still not understanding this. I tried to use your code but it comes up as with true false results so I entered it in an if..then statement like:

if DateAdd('yyyy', (DateDiff('yyyy', (DateAdd('q', 2, {Gf.Gf_Date})), Today)), {Gf.Gf_Date}) > Today
then {Gf.Gf_Amount} else 0

and I expected to get only gifts in this FY to show (others as 0) but it obviously does not work this way. I am such a novice I don't know what to do next.

I need an expression that will show the gift amount if this fy otherwise 0
then another to show the gift amount if last FY else 0
and another for 2 FYs ago
and another for 3 FYs ago

Melissa

[DrewAllen]

This formula calculates FY to parallel date. In other words, if I'm writing a report on YTD figures and I want to compare it to previous fiscal years, I usually want to compare it to the corresponding time periods in those previous fiscal years. So if today were the Ides of March, I would want to compare dates in the following ranges

7/1/07-3/15/08
7/1/06-3/15/07
7/1/05-3/15/06
etc.

This formula tells you whether the gift falls in that corresponding date range regardless of the fiscal year.

For your purposes, I usually create two base formulas

{@Current FY}
Year(DateAdd("q", 2, Today}))

{@Gift FY}
Year(DateAdd("q", 2, {Gift Date}))

Then I'll create additional formulas using these two base formulas.

{@formula 1}
If {@Gift FY} = {@Current FY} Then {Any Value}

{@formula 2}
If {@Gift FY} = {@Current FY} -1 Then {Any Value}

etc.

It's not really necessary to create the two base formulas, but if you have a lot of these other formulas, I find that it's faster and much easier to read.

Some of you may be wondering why I always add quarters rather than months. The reason is that I can never remember whether "m" is months or minutes. Different systems have annoying differences in their syntax for this function. I don't want to have to look up every time whether months is "m", "M", or "mm".

Drew

[Melissa Siobhan]

Okay - I am getting much closer - I have the following

CurrentFY:
DatePart ("yyyy", DateAdd ("q", 2, CurrentDate))

GiftFY:
DatePart ("yyyy", DateAdd ("q", 2, {Gf.Gf_Date}))

CurrentFYGift:
If {@GiftFY} = {@CurrentFY} then {Gf.Gf_Amount}

So now I am stuck with the previous years - I can only figure out how to only get it year to da