Print Page | Close Window

Formula for First time fix within 14 days

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21146
Printed Date: 04 May 2024 at 5:16pm


Topic: Formula for First time fix within 14 days
Posted By: ajp42
Subject: Formula for First time fix within 14 days
Date Posted: 31 Oct 2014 at 3:33am


Good afternoon

Trying to work out a formula

I basically need to create within Crystal - the amount of days between the previous job finished and the next call logged - if this is more than 14 days this is should say "FTF" ( first time fixed ) if less then put "NULL"
This would need to be based on the same Serial No only - as this would have multiple Cust Order Numbers

Below is an example of some data based on the above


Customer order number     Serial number     Call Logged Count     Actual Finish Date     
0005267219     C517206     23/10/2013     29/10/2013       
0005494652     C517206     27/12/2013     27/12/2013 59
0005650378     C517206     15/02/2014     17/02/2014 50
0005724493     C517206     09/03/2014     09/03/2014 20

The count at the end is the amount of days between the logged and finished date - which is what I need it to look like in crystal

I was thinking of using running total maybe - but any ideas would be good

Thanks
Andy
59


Replies:
Posted By: DBlank
Date Posted: 31 Oct 2014 at 7:54am
you can use datediff() with next() or previous()
example:
if next(jobid) <> jobid then '' else
if next(jobid) = jobid and datediff('d',finishdate,next(logdate)) > 14 'FTF' else 'NULL'

Posted By: ajp42
Date Posted: 02 Nov 2014 at 9:58pm

thanks for this - doesn't seem to work though
Below is the example I have put in

if next({SSJOBH.Customer order number}) <> {SSJOBH.Customer order number}then '' else

if next({SSJOBH.Customer order number}) = {SSJOBH.Customer order number} and datediff('d',{@Actual Finish Date},next({@Call Logged}) > 14 'FTF' else 'NULL'

Posted By: z9962
Date Posted: 02 Nov 2014 at 11:01pm
How is it not working? can you give an example?

Posted By: ajp42
Date Posted: 02 Nov 2014 at 11:09pm
The formula doesn't work - brackets I think seem to be the issue but I cannot work out what needs to change

Posted By: DBlank
Date Posted: 03 Nov 2014 at 3:31am
all of these are single quotes - you can use double if you want.
you are also missing a close parenth
 
if next({SSJOBH.Customer order number}) <> {SSJOBH.Customer order number}then '' else
if next({SSJOBH.Customer order number}) = {SSJOBH.Customer order number} and datediff('d',{@Actual Finish Date},next({@Call Logged})) > 14 'FTF' else 'NULL'
 

Posted By: ajp42
Date Posted: 03 Nov 2014 at 4:12am
Thanks for that - this has worked but now has created another problem

The below is the formula I have in query

if next({SSJOBH.Serial number}) <> {SSJOBH.Serial number}then '' else
if next({SSJOBH.Serial number}) = {SSJOBH.Serial number} and datediff('d',{@Actual Finish Date},next({@Call Logged})) > 14 then 'FTF' else ''

The above is based on machine items - but if there is no record after the last one per machine item I need it also to say FTF - as this would mean we have had no more visits - as long as I haven't included it in the above query within 14 days
Also - lines that only have one logged call - has not been counted - so would need to be a FTF
I have sorted the data by Serial Number as there are multiple serial numbers - so the last one would be a FTF if no return

Posted By: DBlank
Date Posted: 03 Nov 2014 at 4:23am
in sample data terms please

Posted By: ajp42
Date Posted: 03 Nov 2014 at 4:37am
Thanks for your help - I have worked this out myself

I may come back to you again



Print Page | Close Window