| Author |
Message |
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
  
Topic: Formula for Reporting Mileage
Posted: 07 Apr 2010 at 11:01am |
I need a formula to report mileage at $0.38 up until 6-30-2009 and $0.50 from 7-01-2009 to the current date.
Thanks for helping me with this!
|
 IP Logged |
|
|
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 07 Apr 2010 at 11:08am |
Sorry, here are the fields I have on the report:
Date = LABTRANS.STARTDATE
Mileage = LABTRANS.LT1
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Apr 2010 at 1:19pm |
if LABTRANS.STARTDATE < date(2009,6,30) then ccurr(LABTRANS.LT1 * .38) else
if LABTRANS.STARTDATE in date(2009,7,1) to currentdate then ccurr(LABTRANS.LT1 * .50)
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 08 Apr 2010 at 4:47am |
|
Thanks for your help DBlank. Unfortunately, I was getting several errors regarding the formula you provided, but it gave me an insight of how the formula should be written. After alittle modification to your formula, I ended up with this:
if {LABTRANS.STARTDATE} < date(2009,6,30) then {LABTRANS.LT1} * .38 else
if {LABTRANS.STARTDATE} in date(2009,7,1) to currentdate then {LABTRANS.LT1} * .50
I was getting errors on the ccurr in your formula, and I had no idea what this was referring to, so I removed it. Since your experience with CR fars exceeds mine, can you explain what it is typically used for?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Apr 2010 at 5:09am |
Sorry, I typed it wrong. It should be ccur not ccurr
it converts the field to currency type
if {LABTRANS.STARTDATE} < date(2009,6,30) then ccur({LABTRANS.LT1} * .38) else
if {LABTRANS.STARTDATE} in date(2009,7,1) to currentdate then ccur({LABTRANS.LT1} * .50)
It is not necessary but since you were dealing with monentary values I threw it in the mix. Edited by DBlank - 08 Apr 2010 at 5:13am
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 08 Apr 2010 at 5:34am |
|
Thanks. I would have never figured that out. The formula you provided works well. Thanks again.
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 08 Apr 2010 at 6:20am |
|
According to our IT dept., things are somewhat different than what I originally mentioned. Could you provide a formula for the following scenario:
{LABTRANS.STARTDATE} Prior dates to 6-30-08; {LABTRANS.LT1} $0.38
{LABTRANS.STARTDATE} 7-1-08 to 12-31-08; {LABTRANS.LT1} $0.59
{LABTRANS.STARTDATE} 1-1-09 to 12-31-09; {LABTRANS.LT1} $0.55
{LABTRANS.STARTDATE} 1-1-10 to 12-31-9999; {LABTRANS.LT1} $0.50
Thanks for your help
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Apr 2010 at 6:25am |
if {LABTRANS.STARTDATE} <= date(2008,6,30) then ccur({LABTRANS.LT1} * .38) else if {LABTRANS.STARTDATE} in date(2008,7,1) to date(2008,12,31)then ccur({LABTRANS.LT1} * .59) else
if {LABTRANS.STARTDATE} in date(2009,1,1) to date(2009,12,31) then ccur({LABTRANS.LT1} * .55) else
if {LABTRANS.STARTDATE} >= date(2010,1,1) then ccur({LABTRANS.LT1} * .50)
Edited by DBlank - 08 Apr 2010 at 6:27am
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 08 Apr 2010 at 6:29am |
|
Works great! Thanks again.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Apr 2010 at 6:32am |
Note I altered the first if statement to use <= rtaher than just < to make sure not to orphan the records with an exact 6-30-08 date.
Make sure yours uses the <= also  |
| IP Logged |
|
|
|
