| Author |
Message |
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
   Topic: Before and after in same Data Row
Posted: 04 Feb 2014 at 8:50am |
Hi,
I'm in Crystal 2008. I have a table that stores all historical information in rows. Like this:
| EMPLOYEE |
FLD_NBR |
BEG_DATE |
N_VALUE |
| 12345 |
729 |
4/5/1999 |
68473.81 |
| 12345 |
729 |
3/1/2012 |
68473.6 |
| 12345 |
729 |
3/1/2013 |
70699.2 |
| 12345 |
729 |
9/30/2013 |
74235.2 |
I need to be able to display the records, indicating the old record and the new record in the same row, with the effective date (which is the BEG_DATE) next to the new value, like this:
| EMPLOYEE |
FLD_NBR |
BEG_DATE |
Old Value |
New Value |
| 12345 |
729 |
3/1/2012 |
68473.81 |
68473.6 |
| 12345 |
729 |
3/1/2013 |
68473.6 |
70699.2 |
| 12345 |
729 |
9/30/2013 |
70699.2 |
74235.2 |
Just looking for a starting place. I have found a LAG function, but I am not sure if it applies to Crystal or how to effectively use it. Thanks!
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
 IP Logged |
|
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 04 Feb 2014 at 9:15am |
|
you could try the previous function
the old value would be
previous (N_VALUE)
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 06 Feb 2014 at 9:47am |
I think this is what I need; however, now I need to tweak the formula a little. The Previous function is also pulling in the previous record even if it’s a different Employee ID in the prior record, and there are many employee IDs in this table. I need the Previous function to reset if the current and previous Employee IDs do not match and If they do not match then return Zero in the “Previous” column. How do I do that?
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 06 Feb 2014 at 11:03am |
|
you could try this
create a running total like
as count of employee IDs for each record reset on change of group
create a formula
if running total <> 1 then previouse(N_VALUE)
Edited by kostya1122 - 06 Feb 2014 at 11:11am
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 06 Feb 2014 at 11:53am |
|
Even easier, try something like this:
If PreviousIsNull({employee ID}) or {employee ID} <> previous({employee ID}) then previous({N_Value})
This way you don't have to do anything with running totals or other stuff.
-Dell
|
|
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 06 Feb 2014 at 4:39pm |
Well, I'm not sure how to construct the Running total, and I've tried the IF statement above and it's still pulling records from the employee ID that appears prior to the current employee ID. I just discovered something about the table I'm pulling from. The data is all scattered. So it's stored something like this:
| EMPLOYEE |
BEG_DATE |
A_VALUE |
| 47697 |
6/9/2008 |
FV113WB |
| 47696 |
6/9/2008 |
FVNC |
| 47697 |
6/17/2013 |
FVMNATC | As you will see, the Employee ID's are not in order. I don't thinkg there's any order to the data. So when I use the previous formula, for Ee ID 47697, it's giving me the A_Value for 47696. So in this example, how do I get my the "previous" formula to recognize that FV113WB is the previous record for employee 47697?
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 07 Feb 2014 at 4:02am |
|
You have a couple of options. The best would be to create a group on Employee ID. Then suppress the group header and footer sections. This will cause your data to be sorted together by employee so that the formulas I gave you will work correctly.
-Dell
|
|
|
|
IP Logged |
|
|
|
