Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Formula for values of the same field over time Post Reply Post New Topic
Author Message
adavis
Senior Member
Senior Member


Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
Quote adavis Replybullet Topic: Formula for values of the same field over time
    Posted: 26 Dec 2014 at 4:28am
Maybe I am over-thinking this because it is the day after a holiday and a Friday, but I'm hoping someone has a solution.

I have a number field {table.reading}.
I also have a date field to correspond to the number field {table.readingdate}.

What I would like to do is calculate the difference between the {table.reading} values over multiple dates.

Example (though obviously not correct)

({table.reading} on {table.readingdate}) - ({table.reading} on {table.readingdate})


How would I go about getting this to work?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Dec 2014 at 5:55am
it appears that this is a multiple row calculation so what you are trying to do is calculate the difference between a min and max value or only on specific dates?
if specifc date then create a formual field to do it for you
if date= firstdate then value else
if date =secondate then value *(-1)
else 0
sum this (likely at a group level to get your value for that group.
You would really need to explain your entire data set more to get more help though.
IP IP Logged
adavis
Senior Member
Senior Member


Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
Quote adavis Replybullet Posted: 29 Dec 2014 at 5:54am
Yes it is a multiple row calculation.
The dates are not specific, rather I need to calculate the difference between the most recent reading and the reading before it. The date is grouped by reading device

So for example:


DEVICE   READ   READ DATE      DIFF
1        8500   12/21/14       500
1        8000   11/18/14       350
1        7650   10/19/14

2        6500   12/26/14       200
2        6300   11/19/14


The data does not have a difference field, I need to calculate that.
IP IP Logged
adavis
Senior Member
Senior Member


Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
Quote adavis Replybullet Posted: 29 Dec 2014 at 8:59am
I am almost at a solution, but can't quite get it to work right.

I created a formula field titled DIFF as the following:
if {table.meter_id}= Previous({table.meter_id}) then
({table.reading}) - previous({table.reading}) else
0


When I place this field in the detail section of the report, I get the difference between two fields, but the calculation is returning the difference in the previous row instead of the most recent row.

Example:
DEVICE / READ / READ DATE /    DIFF
1     /   8500 / 12/21/14    /   0
1     /   8000 / 11/18/14    /   500
1     /   7650 / 10/19/14    /   350

2     /   6500 / 12/26/14    /    0
2     /   6300 / 11/19/14    /   200
IP IP Logged
adavis
Senior Member
Senior Member


Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
Quote adavis Replybullet Posted: 29 Dec 2014 at 9:35am
I figured it out. I replaced "previous" with "next". I didn't realize "next" was a keyword and to me it seemed like previous would have made the most sense since I was subtracting a value from an earlier date.

I don't exactly understand why the solution I came up with works, but it works, so I'm happy enough.
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.016 seconds.