optimized formulas for aging buckets

[smueller72]

Hi all - I have a pretty basic report(at least I thought) that sums invoice totals and puts them into various 'buckets'. Through much trial and error I thought I had gotten the formulas working correctly. However, today, one of our marketing folks noticed an issue with the numbers not adding up properly across the buckets. In looking back at my formulas I realized that I had incorrectly assumed that if you used the DateAdd function to subtract one month from the last day of a given month(2/28 in this case) that the function was 'smart' enough to return the last day of the prior month. This was not that case as it was going back to 1/28 rather than 1/31 as I had assumed.

Rather than add to the, already ungodly, amount of logic(and incorrect logic at that) in these formulas, I thought I would look for some advice here on how to create optimized formulas for the following buckets:

Current MTD
Prior Month
Current YTD
Current Quarter to Date
Prior Quarter to Date
Trailing 6,12,18 months

The invoice cutoff datetime is the parameter provided to the report.

Probably pretty easy stuff for some of your gurus. Any help would be greatly appreciated.

 

[synapsevampire]

In lieu of the dateadd function, use the dateserial, as in:

dateserial(year({table.date}),month({table.date})-1,day({table.date}))

This works as you intended.

I'm guessing that you've already worked out all of these formulas, so replacing the function should handle your problem.

-k

 

[lbass]

Try using the following for the last day of the previous month:

dateserial(year({table.date}),month({table.date}),01)-1

-LB

 

[smueller72]

Thanks for the responses. I've went one step further and decided to create summary tables in the database that summarizes invoice data. This has reduced the number of records by nearly 90%. Now, I have another question about how to accomplish a report request that uses this summary table. Specifically:

The user can enter the month and year that they want to run the report for for comparision purposes. The data should then display side by side on the report itself - i.e year/month 1 invoice count, average should be side by side with year/month 2 invoice count, average so that the user can easily see variances reading left to right. Essentially I have two detailed result sets that need to be displayed side by side the same detail section. Hope this makes sense - I know I may have to been too clear. Any suggestions would be greatly appreciated.

Regards,
Steve Mueller

 

[lbass]

What are the fields in the new table, and what are their datatypes? Can you show a sample of how these fields would display if laid in the detail section?

-LB

 

[smueller72]

Sure - sorry, I should have done that in the first place:


Year 1(user supplied, numeric param) Year 2(user supplied)
Office Number
Jan Invoice Count, Invoice Amount Invoice Count, Amt
Feb Invoice Count, Invoice Amount Invoice Count, Amt
Mar Invoice Count, Invoice Amount Invoice Count, Amt
Totals for Year 1 Totals for Year 2

something along those lines is what the user is looking for. The source table already contains the invoice detail data summed and grouped by office #, year, month. Hopefully this clarifies things a bit. Any help is appreciated.

Regards

 

[lbass]

I think you are showing a display of what you want, not how the current fields would display if laid in the detail section. And what is the datatype for month and year. Is month a string that appears like "Jan" and year is a number? Or is just a datefield that you have grouped into months and years?

I'm not sure, but you could try inserting a crosstab where you use month as the row field, year as the column, and then add invoice count and amount as the summary fields.If these are already summaries, insert a maximum on them. Should work, although if the month is a string, you'll probably need to use specified order on the row field.

This assumes you are using a version of CR that allows horizontal display of summaries (see the customize style tab). If not, you will need to create a manual crosstab.

-LB