If - Then - Month 1

[Cort]

I need some advice as to if I am approaching this problem the correct way.

Using CR 8.5

The table in question is an accounting table structured something like this:

Contract ID - year - pay_1 - Pay_2 - Pay - 3 (etc throught 12) - Change_1 - Change_2 (etc through 12)
110 01 500 0 0
121 02 0 0 100

So, contract 110 would have had a payment of 500 in Jan - 2001. And contract 121 had a payment of 100 in March of 2001. Make sense?

OK here is my delimma. I need to create a report that will show the payments for a particular contract during the last full month and for the same last full month for the previous calander year. I was thinking of creating 24 formulas (one for each month of 2002 and 2001) which would be pretty easy and then 2 master formulas that would look at the current date, figure out what was the last full month and then place the proper formula of the previous 24 in place.

Do you think this would be the best way to approach the issue? I was thinking that there is probably a very elegant way to construct the same thing using an array but have actually been able to formulate it in my head how to create it.

Also if my current thinking is the way to go for the master formula how could I compare the current date and calculate the last full month to get the proper formula returned?

Thanks always to the great advice given here.

 

[synapsevampire]

This doesn't make sense:

"So, contract 110 would have had a payment of 500 in Jan - 2001. And contract 121 had a payment of 100 in March of 2001. Make sense?"

The data shows the March as 02?

I'll assume a typo.

You should be able to just pull in the appropriate data (lastfullmonth and 1 year before lastfullmonth) using the record selection criteria, group on the Contract ID and year, place suppression formulas on the columns to only show the correct month column and display the data.

Perhaps I misunderstand, can you demonstrate how you'd like to see the data?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Cort]

Your correct typo on my part should be 100 in March 2002.

Here is how I would like the data to look, Contract info would be in the details section and suppressed. Grouped by Rep (linked from another table) with a running total for the sum:

Rep: BoB

Paid on contracts in March:

2001 2002 Change
10,000.00 11,500.00 1,500.00

Paid on contracts YTD:

2001 2002 Change
65,000.00 86,000.00 21,000.00

(next Rep, etc)

Hopefully that makes some sense. Am I making too much out of my problem?

 

[synapsevampire]

Thanks, I sort of understand now, except I'm not sure if the CHANGE columns have the previous years data already, or whether we need to have both years and do the math.

I'll assume the latter.

First, the record selection criteria:

{MyTable.Year} >= year(minimum(lastfullmonth))-1

We now have the problem of converting text fields to meaningful date information (bad table design!).

So you can create the 24 formulas as you suggested, or you can build a 3 formulas to do all of the work, and a few more to display, though it's a bit more complicated:

Group Header formula;
whileprintingrecords;
global numbervar LastMonthCurrentYearAmt:=0;
global numbervar LastMonthPreviousYearAmt:=0;
global numbervar LastMonthYTDAmt:=0;
global numbervar LastMonthPreviousYTDAmt:=0;

Detail section formula;
whileprintingrecords;
global numbervar LastMonthCurrentYearAmt;
global numbervar LastMonthPreviousYearAmt;
global numbervar LastMonthYTDAmt;
global numbervar LastMonthPreviousYTDAmt;
numbervar LastMonthCurrentYear:= year(minimum(lastfullmonth))
numbervar LastMonthPreviousYear:= year(minimum(lastfullmonth))-1
numbervar LastMonth := month(minimum(lastfullmonth));
numbervar array MonthValues := [{MyTable.Pay-1},{MyTable.Pay-2},{MyTable.Pay-3} etc...]

//LASTFULLMONTH amount
If LastMonth = 1 and {MyTable.MyYear} = LastMonthCurrentYear then
LastMonthCurrentYearAmt := LastMonthCurrentYearAmt+{MyTable.Pay-1}
else
If LastMonth = 2 and {MyTable.MyYear} = LastMonthCurrentYear then
LastMonthCurrentYearAmt := LastMonthCurrentYearAmt+{MyTable.Pay-1}
else
If LastMonth = 3 and {MyTable.MyYear} = LastMonthCurrentYear then
LastMonthCurrentYearAmt := LastMonthCurrentYearAmt+{MyTable.Pay-1}
etc...

//Last year LASTFULLMONTH amount
If LastMonth = 1 and {MyTable.MyYear} = LastMonthPreviousYear then
LastMonthPreviousYearAmt := LastMonthPreviousYearAmt+{MyTable.Pay-1}
else
If LastMonth = 2 and {MyTable.MyYear} = LastMonthCurrentYear then
LastMonthPreviousYearAmt := LastMonthPreviousYearAmt+{MyTable.Pay-1}
else
If LastMonth = 3 and {MyTable.MyYear} = LastMonthCurrentYear then
LastMonthPreviousYearAmt := LastMonthPreviousYearAmt+{MyTable.Pay-1}
etc...

//Last Month Year Amount
if {MyTable.MyYear} = LastMonthCurrentYear
for x := 1 to lastmonth do(
LastMonthYTDAmt := LastMonthYTDAmt+MonthValues[x]
)

//Last Month Previous Year Amount
if {MyTable.MyYear} = LastMonthPreviousYear
for x := 1 to lastmonth do(
LastMonthPreviousYTDAmt := LastMonthPreviousYTDAmt+MonthValues[x]
)

Group Footer Formula(s):
whileprintingrecords;
global numbervar LastMonthCurrentYearAmt;
global numbervar LastMonthPreviousYearAmt;
global numbervar LastMonthYTDAmt;
global numbervar LastMonthPreviousYTDAmt;
//bring in only those you need and display the result as
//in the following
LastMonthCurrentYearAmt

Now that you have 4 variables with the appropriate numbers, you can subtract to get differences.

And to get the last month name, just use:

monthname(LastMonth)

Left a bit there for you to flesh out, but that should work.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Cort]

Thanks SV. That took a lot of work on your part and I appreciate it. I thought it could be done a lot more elegant than my original idea. Your suggestion worked great.

I do have a question though. You and several others always preface almost every formula with:

whileprintingrecords

why?

 

[synapsevampire]

whileprintingrecords isn't required, but it explicitly states that the formula is to be processed when it is printing the database record data, as opposed to the whilereadingrecords.

Sometimes I omit it still.

A lot less work than you might imagine, I'm purdy good with copy and paste

Glad it worked out.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com