Author |
Message |
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Topic: Crystal Report Date Posted: 31 Aug 2010 at 5:30am |
I want to show the number of orders that have been open for more than a week. So I have in the formula workshop 'Date '. I need help to create a formula that shows if date is 7 days from report date run time. Thanks for your help.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Aug 2010 at 5:44am |
if datediff('d',table.datefield,currentdate)>6 then 'Late' else ''
|
IP Logged |
|
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Posted: 31 Aug 2010 at 9:02am |
This shows that I have an error in the formula, want I want is it to come up with a number so I can say
## PO's Have Been Out Longer Than 1 week
Current:
if datediff('d',PURCHORDER.PO_DATE,currentdate)>6 then 'Late' else
Thanks for all your help.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Aug 2010 at 9:08am |
so you want to do data selection on it rather than return a text?
datediff('d',PURCHORDER.PO_DATE,currentdate)>6
or
datediff('d',PURCHORDER.PO_DATE,currentdate)
will show you then # value of the days
|
IP Logged |
|
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Posted: 31 Aug 2010 at 9:16am |
I just learned that this is not a indexed as a date/time field, but as a text field. If there anything else I can do.
Thanks
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Aug 2010 at 9:29am |
what does the text look like?
|
IP Logged |
|
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Posted: 01 Sep 2010 at 10:32am |
This formula should work:
datediff('d',{PURCHORDER.PO_DATE},datadate)>7
It shows a false because it is taking the last PO Date. For example I have a PO from 8/17 and one from 8/29 I want this to say true that there is a PO that has been out longer than one week so I want it to look at the PO with the 8/17 date and saying True for PO's. I hope this makes sense.
Thanks
Edited by ridershaun - 01 Sep 2010 at 10:44am
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Sep 2010 at 10:58am |
Not making sense.
Do you mean you have multiple recorsd fpr the same PO# and you need to find it where the first date for the all the rows for that PO is >7 ?
|
IP Logged |
|
ridershaun
Newbie
Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
|
Posted: 02 Sep 2010 at 1:50am |
I have multiple PO's from multiple dates so I have dates of 8/23, 8/17, 8/20, 8/27 and I want on the report to show if there are any PO's that have been open for more than seven days from the day the report is run. But with the current:
datediff('d',{PURCHORDER.PO_DATE},datadate)>7
it is showing a False because it has not been 7 days from the 8/27 PO, I need it to show a True because the 8/17 PO has been open for more than seven days.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 Sep 2010 at 5:41am |
the formula of
datediff('d',{PURCHORDER.PO_DATE},datadate)>7
is supposed to be used in the select expert. It evalautes each row to see if it meets the condition (TRUE) or not (FALSE)
if it is TRUE it includes it in the report. If it is Flase it excludes it.
What are you doing with it now?
|
IP Logged |
|
|