Date Formula Needes
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=7587
Printed Date: 07 May 2024 at 5:56pm
Topic: Date Formula Needes
Posted By: lahorichick
Subject: Date Formula Needes
Date Posted: 03 Sep 2009 at 9:09am
|
Ok...i have a more urgent report to do now..lol
I need to show all accounts we haven't done business with for a minimum 5 years.
I have the Invoice file with Invoice.Date = the day invoice was created.
I figure invoice.date has be older than 9/1/2004.
But, where /how do i put in...if there was an invoice for after 2004 anywhere in the account- don't mention that account...HELPPP
|
Replies:
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 9:30am
|
the main jist of the formula
if account.number contains {invoice.date} > Date (9/5/2005) then do not show that account. number
else show the account.number
thats the basic dumb version i think of before getting techi
|
Posted By: DBlank
Date Posted: 03 Sep 2009 at 9:35am
|
You can use the same premise as I gave you before but decide if you want hat process based on potential group changes after the data selection process.
group on acct#
Formula field if inoice date > 9-5-05 then 1 else 0
sum on that at acct# group
group selection criteria NOT(SUM(formulafield,account#)>0)
|
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 9:41am
| where am i placing the Not(....) in a new formula and create a group on that? i can't... |
Posted By: DBlank
Date Posted: 03 Sep 2009 at 9:43am
|
Sorry,
IN your select expert but make sure to change it to "group selection"
then use NOT(summary>0)
|
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 9:50am
| its grouping items as 0 that have newer invoices? |
Posted By: DBlank
Date Posted: 03 Sep 2009 at 9:55am
|
Nope. The opposite. I think the NOT in the select statment is messing you up...
You are gouping on Acct#.
then you are flaging any row in the group where the date is > 9-5-05 with a 1.
Then you sum that "flag" at the group level. Any group > 0 means it should be exluded so that becoems your select criteria (but it is at the group level not the row level). Slect criteria have to return TRue (include) or FALSE (exclude).
To make it easier you can just use:
summary=0
instead of the NOT i gave you earlier. I did that because in some cases summaries are not done at all (NULL) and the NOT accounted for that but I don't think you need to worry about that here.
|
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 10:11am
|
its going to continue to show me the '1' accounts in the left-hand drill down menu- but won't let me access them correct.
i have this running through invoices--which is taking forever...cuz it has to go through all the invoices...what would be faster? the billing number? |
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 10:26am
| ah..ya...the time frame is supposed to be accounts lost within the last 5 years...lol??? |
Posted By: DBlank
Date Posted: 03 Sep 2009 at 10:35am
|
Yep it will still show the account numbers in the group tree but not in the report.
Unless you have some other criteria like active in the last ten years and lost in the last 5 you cannot speed it up much. If you have access to creating views or stored procs in the DB you could do it faster that way (same as with your other report) but in crystal I don't know if there is a better/faster way.
|
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 10:36am
|
my new criteria is that we lost WITHIN the last 5 years--and we have not invoiced since dec 2008 |
Posted By: DBlank
Date Posted: 03 Sep 2009 at 11:14am
| Do you need help adding that criteria in? |
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 11:14am
| Yes SIR...plz..my mind is drying out...with all these reports |
Posted By: DBlank
Date Posted: 03 Sep 2009 at 11:23am
|
That has me confused.
We defined "lost" as no invoice date in the last 5 years, correct?
and they want no invoice date since Dec 08? Whay not just use that instead of the last 5 years? What did I miss here?
|
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 11:24am
|
lol...
I have so so all account where we have not invoiced within the last 5 years (so if we invoice in 2002--dont' show me the account AND if we invoiced in 2009 dont show me the account)
|
Posted By: DBlank
Date Posted: 03 Sep 2009 at 11:41am
|
Now my mind is dried out....
I can't follow. I need some sample data that should be included and excluded and why please.
|
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 11:56am
|
no prob.
I.) if we invoiced account before 12/1/2003 but never invoiced between 12/2/2003 - 12/1/2008 -- DO NOT SHOW DATA (meaning 1)
II.) if we invoiced account after 12/1/2008 do not include (meaning 1)
III.) if we invoiced ONLY between 12/1/2003 - 12/1/2008 - show me those account Names (meaning 0)
A/C #1
Invoiced: 5/2009 - do not show A/C #1
A/C #2
Invoiced: 6/2004 - show
A/C #3
Invoiced: 2/2002 - do not show
A/C #4
Invoiced : 2/2002 AND 6/2004 - SHOW
I was using these formulas:
Date: if not ({INVOICE.DATE} in Date (2003, 12, 01) to Date (2008, 12, 01)) then 1
else 0 Date2: if not ({INVOICE.DATE} in Date (2003, 12, 01) to Date (2008, 12, 01)) then 2 else 3
Date3: if not ({INVOICE.DATE} > date (12/1/2003) )then 4 else 5
Comparison: if mailto:%7b@Date - {@Date } = 1 and( mailto:%7b@Date - {@Date 2} = 2 or mailto:%7b@Date - {@Date 3} = 5 )then 1 else 0
group #1- comparison
what am i doing wrong?
|
Posted By: lahorichick
Date Posted: 03 Sep 2009 at 11:58am
|
i amost forgot--
Record Select Sum of @Comparison = 0 |
Posted By: DBlank
Date Posted: 03 Sep 2009 at 12:15pm
|
You won't be able to do this that way. You started down the right path with your flags but you need to keep them seperate and then do an and stament looking at SUms of each. I have to run to a meeting but can check in later but the gyst is
date1= if ({INVOICE.DATE} in Date (2003, 12, 01) to Date (2008, 12, 01)) then 1 else 0
date2=if {INVOICE.DATE} < Date (2003, 12, 01) 1 else 0
date3=if {INVOICE.DATE} > Date (2008, 12, 01) 1 else 0
group select statement would be :
SUM(date1,group1)=0 and SUM(date1,group1>0) and SUM(date3,group1)=0
|
Posted By: DBlank
Date Posted: 03 Sep 2009 at 7:50pm
|
OK now that I have a minute I think you can simplify this and that the above suggestion was wrong and probably did not return any results at all.
if ({INVOICE.DATE} in Date (2003, 12, 01) to Date (2008, 12, 01)) then 0 else 1
SUm on that at the group of invoice#
use the select expert on group selection
SUM(@date,group1)=0
This should exclude all records that had any activity before 12-1-03 or after 12-1-08.
That is what you need right?
|
Posted By: lahorichick
Date Posted: 04 Sep 2009 at 4:00am
|
but lets say an account had activity in 2005 and 2001 I would still like to see that account, because the last time i did business with them was in 2005.
|
Posted By: lahorichick
Date Posted: 04 Sep 2009 at 4:40am
|
this is what the select expert is reading:
Sum ( mailto:%7b@Date - {@Date 3}, {BILLACCT.NAME}) = 0.00 and
Sum ( mailto:%7b@Date - {@Date 2}, {BILLACCT.NAME}) > 0.00 and Sum ( mailto:%7b@Date - {@Date }, {BILLACCT.NAME}) = 0.00 @Date = if ({INVOICE.DATE} in Date (2003, 01, 01) to Date (2008, 12, 31)) then 1 else 0
@Date2 = if {INVOICE.DATE} < Date (2003, 01, 01) then 1 else 0
@Date3= if {INVOICE.DATE} > Date (2008, 12, 31) then 1 else 0
I'm looking through the results that I have---and its ONLY invoices for 2002--nothing else?
|
Posted By: lahorichick
Date Posted: 04 Sep 2009 at 4:47am
|
ok the last formula you gave:
if ({INVOICE.DATE} in Date (2003, 12, 01) to Date (2008, 12, 01)) then 0 else 1
looks right--i ran it..the results look like...nothing for 2002 picked up nothing for 2009 picked up
but i want to confirm with you...if an account was invoiced in 2002 and then in 2007 that WILL come up in these results yes?
|
Posted By: lahorichick
Date Posted: 04 Sep 2009 at 7:33am
|
OK..so i had to revert back to those 3 seperate forumla..it worked..i'm done...atleast with this one..back to the other report...(hopefully not until Monday)
thanks for ur help
|
Posted By: DBlank
Date Posted: 04 Sep 2009 at 7:34am
|
lol. I was in the middle of a really long reply explaining to use the 3 formulas again.
Glad it worked out
 |