Talk with the Author
 Crystal Reports Forum : General Information : Talk with the Author
Message Icon Topic: Date Formula Question Post Reply Post New Topic
Author Message
bucky7199
Newbie
Newbie
Avatar

Joined: 17 Feb 2008
Location: United States
Online Status: Offline
Posts: 30
Quote bucky7199 Replybullet Topic: Date Formula Question
    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.
IP IP Logged
bucky7199
Newbie
Newbie
Avatar

Joined: 17 Feb 2008
Location: United States
Online Status: Offline
Posts: 30
Quote bucky7199 Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.