| Author |
Message |
leke
Newbie
Joined: 04 Aug 2014
Online Status: Offline
Posts: 6
|
  
Topic: How to Calculate average for datediff
Posted: 04 Aug 2014 at 4:56am |
So I have this statement that calculates the time difference. It uses the previous mod_date minus the current mod_date. Now I would like to get the average of each users time interval, basically average of DateDiff. Below is the SQL to get the time interval: (DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60I've tried something like: Select AVG(DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60But I keep getting prompted for some numerical value on AVG(). If I type out Average instead of AVG for this statement: Select Average(DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60 I get, "A field is required here" message, any ideas??
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 05 Aug 2014 at 5:08am |
|
previous doesn't mean anything in a SQL context, and Select doesn't mean anything in CR.
it sounds like you want a formula that is like:
avg(datediff(....),{grouping criteria}).
the big logic problem that I see is the previous value...previous doesn't care about group boundaries.
you could calculate this yourself using shared variables which would also give you more control over the logic, like:
shared numbervar cnt;
shared numbervar sm;
if previous({table.groupid}) = {table.groupid} then(
sm := sm + {table.value};
cnt := cnt + 1
)
"" //so nothing is displayed
where you want the avg to display:
shared numbervar cnt;
shared numbervar sm;
if cnt > 0 then
sm/cnt
else
0
and of course there is the reset in the group header
shared numbervar cnt:=0;
shared numbervar sm:=0;
""
hope this gives you some ideas of how to approach the solution that works for you.
|
| IP Logged |
|
leke
Newbie
Joined: 04 Aug 2014
Online Status: Offline
Posts: 6
|
Posted: 05 Aug 2014 at 6:03am |
I have a Formula field called lapsed time that calculates the datediff with the date diff code. What if I perform summary functions specific to the grouping? Local NumberVar TotalSec := Average({@lapsed time},{PROD_TRKG_TRAN.MOD_DATE_TIME});
Local NumberVar Days := Truncate (TotalSec / 86400);
Local NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
Local NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
Local NumberVar Seconds := Remainder ( TotalSec , 60);
Totext ( Days, '00', 0,') + ':'+
Totext ( Hours, '00', 0,') + ':'+
Totext ( Minutes,'00', 0,') + ':'+
Totext ( Seconds,'00', 0,'Am I on the right track? I do get "The ) is missing".
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 05 Aug 2014 at 6:17am |
|
that might work...
I never know, as 'lapsed time' is calculated at run time...
so it probably won't work...you can't aggregate a formula, though this one might work, but with confused results as the previous will probably cross the group boundary.
What to know about aggregates in CR...the data needs to be in 'dataset' in a raw form. So your datediff(s,previous({}), {}) should work as the data is there...I am just not sure how CR will use the previous in the calculations.
HTH
it's worth a try
|
| IP Logged |
|
leke
Newbie
Joined: 04 Aug 2014
Online Status: Offline
Posts: 6
|
Posted: 05 Aug 2014 at 6:44am |
This code below works: (DateDiff ("s",previous({PROD_TRKG_TRAN.MOD_DATE_TIME}) ,{PROD_TRKG_TRAN.MOD_DATE_TIME}))/60All it does is takes the current mod_date_time and subtracts the previous mod_date_time, and returns the values in "seconds".
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 06 Aug 2014 at 4:56am |
|
yeah, I know what it does...
you were asking how to do something else...like calculate an average based on a formula, which may or may not work, and i included the provisos as to why it may not...so that you could test it and decide for yourself.
so I guess the answer to the original post is yes, I think that you are on the right track.
|
| IP Logged |
|
leke
Newbie
Joined: 04 Aug 2014
Online Status: Offline
Posts: 6
|
Posted: 07 Aug 2014 at 2:40am |
|
I added the paren to the last totext and get the same error "The ) is missing".
Local NumberVar TotalSec := Average({@lapsed time},{PROD_TRKG_TRAN.MOD_DATE_TIME});
Local NumberVar Days := Truncate (TotalSec / 86400);
Local NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
Local NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
Local NumberVar Seconds := Remainder ( TotalSec , 60);
Totext ( Days, '00', 0,') + ':'+
Totext ( Hours, '00', 0,') + ':'+
Totext ( Minutes,'00', 0,') + ':'+
Totext ( Seconds,'00', 0,')
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 07 Aug 2014 at 4:58am |
|
shouldn't totext be:
totext(Days, '00',0,'')
it looks like a single quote in the post, otherwise it looks correct.
if that doesn't do, this is what I would try...
start commenting out lines, from the bottom up until the error goes away. the last line commented out will have the error.
HTH
|
| IP Logged |
|
leke
Newbie
Joined: 04 Aug 2014
Online Status: Offline
Posts: 6
|
Posted: 07 Aug 2014 at 6:29am |
Made the changes and this is what I get: 
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 08 Aug 2014 at 5:01am |
|
which goes back to my original response.
Since all of the data is not 'intrinsic' to the raw data...or all on the same row of data (previous is not on the same row) then the formula cannot be aggregated by CR.
The way around this is to used shared variables...maybe running totals (they are not my forte, but DBlank's)
with shared variables you to something like:
group header
shared numbervar cnt:=0;
shared numbervar sm:=0;
""
group footer, where you want to display the average:
shared numbervar cnt;
shared numbervar sm;
if cnt>0 then
sm/cnt
else
0
details, this is where the fun is...
probably just update your lapsed time formula to be something like
shared numbervar cnt;
shared numbervar sm;
if (some criteria) then (
cnt := cnt +1;
sm := sm + ({table.field}-previous(table.field));
)
HTH
""
|
| IP Logged |
|
|
|
