Date Difference 1

[Tdharris]

I need to find the difference between dates each time an activity was performed. My report is grouped by unitid(location), then ActivityCode(activity perfomed at the location). I need the difference of the {HISTORY.COMPDTTM}(date the activity was completed)for each instance of each location and each activity. The data looks like this:

GC0003 Arrowhead Elementary School
Completed Date WO# Completed By Activity/Description

6/11/10 940960 PR2203/MOWING TIER 3
6/14/10 942707 PR2203/MOWING TIER 3
6/28/10 945317 PR2203/MOWING TIER 3
7/12/10 940529 PR2203/MOWING TIER 3

6/17/10 943131 PR2405/CHEMICAL APP
7/9/10 951127 PR2405/CHEMICAL APP

7/30/10 960379 TRAVIS JONES PR5059/INSPECT SCHOOL
Thanks in advance,
TDH

TDH
Business Application Specialist

 

[lbass]

datediff("d", minimum({HISTORY.COMPDTTM},{table.activitycode}), maximum({HISTORY.COMPDTTM},{table.activitycode}))

-LB

 

[Tdharris]

Thanks LB, is there a way to get the difference per instance? Calculate the timeframe between each date an activity was performed.

For example:
9/2
9/16 - 8 Days
10/5 - 19 Days
10/25 - 20 Days

Thanks for your help- you are awesome!


TDH
Business Application Specialist

 

[lbass]

if not onfirstrecord and
{table.activitycode} = previous({table.activitycode}) and
{table.unitid} = previous({table.unitid}) then
datediff("d", previous({HISTORY.COMPDTTM},{HISTORY.COMPDTTM})

-LB

 

[Tdharris]

I keep getting "too many arguments have been given to this function" - formula:

if not onfirstrecord and
{ACTDEFN.ACTCODE} = previous({ACTDEFN.ACTCODE}) and
GroupName ({COMPLAND.UNITID}) = previous({COMPLAND.UNITID}) then
datediff("d", previous({HISTORY.COMPDTTM},{HISTORY.COMPDTTM}))

TDH
Business Application Specialist

 

[Tdharris]

Nevermind, not sure why it didnt work at first! Works fine!


Thanks again LB!

TDH
Business Application Specialist

 

[lbass]

There is a missing paren--sorry. Should have been:

if not onfirstrecord and
{ACTDEFN.ACTCODE} = previous({ACTDEFN.ACTCODE}) and
{COMPLAND.UNITID} = previous({COMPLAND.UNITID}) then
datediff("d", previous({HISTORY.COMPDTTM}[red])[/red],{HISTORY.COMPDTTM}))

You also don't need to use the groupname field--just use the field you are grouping on directly.

-LB

 

[Tdharris]

This formula works awesome getting me the date frequency in the details. However, I have been trying to put it into a crosstab summary in the report header per activity code. Here is the formula as it is written now in the details.

if not onfirstrecord and{ACTDEFN.ACTCODE} = previous({ACTDEFN.ACTCODE})then datediff("d", previous({HISTORY.COMPDTTM}),{HISTORY.COMPDTTM})

Is it possible to get the summary per activity? Will it work in the Report Header?

Thanks,
Terena

TDH
Business Application Specialist

 

[lbass]

I think you would have to use variables to collect the results in the code group footer, and then save the report as an unlinked subreport that you place in the report header of the original report, where you suppress all but the group footers. Create these formulas:

//{@reset} for the code group header (suppress the formula):
whileprintingrecords;
numbervar sumdiff;
if not inrepeatedgroupheader then
sumdiff := 0;

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
numbervar sumdiff := sumdiff + (
if not onfirstrecord and{ACTDEFN.ACTCODE} = previous({ACTDEFN.ACTCODE})then datediff("d", previous({HISTORY.COMPDTTM}),{HISTORY.COMPDTTM})
);

//{@display} to be placed in the code group footer:
whileprintingrecords;
numbervar sumdiff;

-LB

 

[Tdharris]

Thanks again for the help. I will give it a try.

TDH

TDH
Business Application Specialist