Print Page | Close Window

Date Formula Question

Printed From: Crystal Reports Book
Category: General Information
Forum Name: Talk with the Author
Forum Discription: Ask Brian questions about his books and give him your comments. Like the book? Hate the book? Have suggestions? Let me know!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3557
Printed Date: 20 Apr 2025 at 12:58am


Topic: Date Formula Question
Posted By: bucky7199
Subject: Date Formula Question
Date Posted: 30 Jun 2008 at 11:04am
I am creating a Cash Flow statement for my company using Crystal Reports XI. It will be a Cash Flow report showing changes in sources and uses of cash from month to month. I have come up with a formula that calculates last months account balance for each account. I created a parameter called date that allows the user to enter the date for the report. Then one formula figures the balance for the account for this month, and the formula below calculates the formula for last month. Then I sum them and subtract them to get the change in the account. To get last months number I simply subtracted 1 from the month. This works great for months Feb through December. However, I can't figure out what I need to do for the month of January. Because, for the month of January, 1 - 1 = 0, not 12. How can I change my formula below to account for this condition that only occurs once per year:
 
//this works for last month
if {GLPerBal.FiscalPeriod} = month({?Date})-1 and {GLPerBal.FiscalYear} = year({?Date}) and   {GLPerBal.Chart} in ["105000"] then
 if {GLPerBal.FiscalPeriod} <> 0 then
   {GLPerBal.CreditAmt} + {GLPerBal.DebitAmt}


-------------
Dan S.


Replies:
Posted By: bucky7199
Date Posted: 01 Jul 2008 at 12:14pm
I solved my own question (guess I just had to get away from my computer for a little bit) the modified formula is below. Changes are in red:
 

//this works for finding last month and the January exception

if {GLPerBal.FiscalPeriod} <> 0 then

if {GLPerBal.FiscalPeriod} = month({?Date})-1 and {GLPerBal.FiscalYear} = year({?Date}) and {GLPerBal.Chart} in ["401000"] then

{GLPerBal.CreditAmt} + {GLPerBal.DebitAmt}

else if

{GLPerBal.FiscalPeriod} = 12 and {GLPerBal.FiscalYear} = year({?Date})-1 and {GLPerBal.Chart} in ["401000"] then

// if {GLPerBal.FiscalPeriod} <> 0 then

{GLPerBal.CreditAmt} + {GLPerBal.DebitAmt}



-------------
Dan S.


Print Page | Close Window