If your fiscal periods are the same as your calander months aren't both
columns of your report such as prior fiscal period and prior month the same?
Also a thanks to Doug Williams at Epicor for the help with the formulas, if
you have been following this thread check out the CurrentMonthLastDay
calculation.
Also note these formulas attempt to figure out beginning and ending dates
with out doing long string calculations and IFF statements because when you
go this route you have to watch for year ends, leap years, etc...
Summary:
CurrentMonthFirstDay = TODAY() - DAY(TODAY()) + 1
CurrentMonthLastDay = TODAY() + (32 - DAY(TODAY())) -
DAY(TODAY() + (32 - DAY(TODAY())))
LastMonthLastDay = TODAY() - DAY(TODAY())
LastMonthFirstDay = DATE(SUBSTRING(STRING(LastMonthLastDay),1,3) +
STRING("01") + SUBSTRING(STRING(LastMonthLastDay),6,3))
NextMonthFirstDay = CurrentMonthLastDay + 1
Patrick Winter
sSc Specialty Screw Corporation
Vantage 5.10.130, Progress 9.1C
-----Original Message-----
From: Todd Hofert [mailto:todd@...]
Sent: Thursday, October 02, 2003 7:35 AM
To: pjw@...
Subject: RE: [Vantage] Report Builder Date Calc
Our fiscal periods are simply 01=January, 02=February, etc.
-----Original Message-----
From: pjw@... [mailto:pjw@...]
Sent: Wednesday, October 01, 2003 3:33 PM
To: Todd Hofert
Subject: RE: [Vantage] Report Builder Date Calc
Give me a minute, I'm getting a headache.
Your going to create a calculated field to figure out the 2002 or 2003 and
insert it in your existing MTD2002 and MTD2003 calculated fields. You may
want to call your calculated fields (MTD2002) This_Year_MTD and
Last_Year_MTD since we want the dates calculated automatically.
We are also going to try to figure out the "09" fiscal period in the formula
automatically. By creating a calculated field and inserting in there also.
Calculated Field Name = Last_Year_MTD IFF(InvoiceHead.FiscalYear = Last_Year
And InvcHead.FiscalPeriod = Last_Year_Fiscal_Period ,ExtPrice,0)
That't how they get inserted now we need to figure out the formula for:
Last_Year and Last_Year_Fiscal_Period.
Last_Year will be something like:
YEAR(TODDAY() - 365)
Fiscal Period I'm not sure how you are setup?
Patrick Winter
-----Original Message-----
From: Todd Hofert [mailto:todd@...]
Sent: Wednesday, October 01, 2003 1:23 PM
To: pjw@...
Subject: RE: [Vantage] Report Builder Date Calc
Patrick,
My current criteria are as follows:
Calculated Field Name = MTD2002
IFF(InvoiceHead.FiscalYear=2002 And InvcHead.FiscalPeriod=09,ExtPrice,0)
Calculated Field Name = MTD2003
IFF(InvoiceHead.FiscalYear=2003 And InvcHead.FiscalPeriod=09,ExtPrice,0)
Calculated Field Name = YTD2002
IFF(InvoiceHead.FiscalYear=2002 And InvcHead.FiscalPeriod<=09,ExtPrice,0)
Calculated Field Name = YTD2003
IFF(InvoiceHead.FiscalYear=2003 And InvcHead.FiscalPeriod<=09,ExtPrice,0)
This sticks the invoices into the appropriate "bucket". I then aggregate
each "Bucket" and print it on the report. How then do I implement your code
listed below (calendar fiscal year) to make these "buckets" to total on?
Thanks
Todd
-----Original Message-----
From: pjw@... [mailto:pjw@...]
Sent: Wednesday, October 01, 2003 2:14 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Date Calc
Last Day of Last Month is = LDLM = TODAY() - DAY(TODAY)
First Day of Last Month is = FDLM = DATE(SUBSTRING(STRING(LDLM),1,3) +
STRING("01") + SUBSTRING(STRING(LDLM),6,3))
My fiscal year starts July 1st, This is for Fiscal Year Start Date:
IIF(MONTH(TODAY()) > 6, DATE("07/01/" +
SUBSTRING(STRING(TODAY()),7,2)),DATE("07/01/" +
SUBSTRING(STRING(TODAY()),7,2)) - 365)
Patrick Winter
-----Original Message-----
From: Todd Hofert [mailto:thofert@...]
Sent: Wednesday, October 01, 2003 1:09 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Date Calc
OK then, while we are on the subject. I have a dozen sales reports that I
run at the beginning of each month for the previous month. These reports
have calculations using InvoiceHead.Fiscal Year and Fiscal Period to total
sales dollars by Last Month Month to Date, Last Year Last Month Month to
Date, Year to Date through Last Month and Last Year Year to Date through
Last Month. Problem is I have to manually edit each of these dozen or so
reports each month to plug in the appropriate fiscal period. It is a hassle.
Anyone know how I can create calculations a filters that will do this
without my intervention?
Thanks
Todd Hofert
IT Director
Spartan Graphics, Inc.
-----Original Message-----
From: Podlin, Michael [mailto:michael.podlin@...]
Sent: Wednesday, October 01, 2003 1:52 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Date Calc
Create two calculated fields MonthStart and MonthEnd
MonthStart:
Date_rb + 1
MonthEnd:
IIF(MONTH((Date_rb + 1)) = 12, DATE("01/01/" + STRING(YEAR(Date_rb) + 1,
"9999")), DATE(STRING(MONTH((Date_rb + 1)) + 1, ">9") + "/01/" +
STRING(YEAR(Date_rb), "9999")))
Then set your filter to quotes >= MonthStart and quotes < MonthEnd
This should work as long as it is run on the last day of the month,
otherwise you can create a long MonthStart which works off the current day
and moves it to the next month.
-----Original Message-----
From: Judy Havlik [mailto:judy.havlik@...]
Sent: Wednesday, October 01, 2003 12:40 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder Date Calc
I'm have a report that will run using the AutoRpt Utility and will pull all
quotes set to expire next month. For instance, the report will be run on
September 30, 2003 and should pull only those quotes that are set to expire
from October 1, 2003 to October 31, 2003.
Can anyone help me with the filter/calculation fields to do this? I can't
seem to get it to work properly.
Sincerely,
Judy Havlik
Plitek, L.L.C.
69 Rawls Road
Des Plaines, IL 60018
Fax: 847-827-6733
PH: 847-827-6680 x223
www.plitek.com
[Non-text portions of this message have been removed]
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
columns of your report such as prior fiscal period and prior month the same?
Also a thanks to Doug Williams at Epicor for the help with the formulas, if
you have been following this thread check out the CurrentMonthLastDay
calculation.
Also note these formulas attempt to figure out beginning and ending dates
with out doing long string calculations and IFF statements because when you
go this route you have to watch for year ends, leap years, etc...
Summary:
CurrentMonthFirstDay = TODAY() - DAY(TODAY()) + 1
CurrentMonthLastDay = TODAY() + (32 - DAY(TODAY())) -
DAY(TODAY() + (32 - DAY(TODAY())))
LastMonthLastDay = TODAY() - DAY(TODAY())
LastMonthFirstDay = DATE(SUBSTRING(STRING(LastMonthLastDay),1,3) +
STRING("01") + SUBSTRING(STRING(LastMonthLastDay),6,3))
NextMonthFirstDay = CurrentMonthLastDay + 1
Patrick Winter
sSc Specialty Screw Corporation
Vantage 5.10.130, Progress 9.1C
-----Original Message-----
From: Todd Hofert [mailto:todd@...]
Sent: Thursday, October 02, 2003 7:35 AM
To: pjw@...
Subject: RE: [Vantage] Report Builder Date Calc
Our fiscal periods are simply 01=January, 02=February, etc.
-----Original Message-----
From: pjw@... [mailto:pjw@...]
Sent: Wednesday, October 01, 2003 3:33 PM
To: Todd Hofert
Subject: RE: [Vantage] Report Builder Date Calc
Give me a minute, I'm getting a headache.
Your going to create a calculated field to figure out the 2002 or 2003 and
insert it in your existing MTD2002 and MTD2003 calculated fields. You may
want to call your calculated fields (MTD2002) This_Year_MTD and
Last_Year_MTD since we want the dates calculated automatically.
We are also going to try to figure out the "09" fiscal period in the formula
automatically. By creating a calculated field and inserting in there also.
Calculated Field Name = Last_Year_MTD IFF(InvoiceHead.FiscalYear = Last_Year
And InvcHead.FiscalPeriod = Last_Year_Fiscal_Period ,ExtPrice,0)
That't how they get inserted now we need to figure out the formula for:
Last_Year and Last_Year_Fiscal_Period.
Last_Year will be something like:
YEAR(TODDAY() - 365)
Fiscal Period I'm not sure how you are setup?
Patrick Winter
-----Original Message-----
From: Todd Hofert [mailto:todd@...]
Sent: Wednesday, October 01, 2003 1:23 PM
To: pjw@...
Subject: RE: [Vantage] Report Builder Date Calc
Patrick,
My current criteria are as follows:
Calculated Field Name = MTD2002
IFF(InvoiceHead.FiscalYear=2002 And InvcHead.FiscalPeriod=09,ExtPrice,0)
Calculated Field Name = MTD2003
IFF(InvoiceHead.FiscalYear=2003 And InvcHead.FiscalPeriod=09,ExtPrice,0)
Calculated Field Name = YTD2002
IFF(InvoiceHead.FiscalYear=2002 And InvcHead.FiscalPeriod<=09,ExtPrice,0)
Calculated Field Name = YTD2003
IFF(InvoiceHead.FiscalYear=2003 And InvcHead.FiscalPeriod<=09,ExtPrice,0)
This sticks the invoices into the appropriate "bucket". I then aggregate
each "Bucket" and print it on the report. How then do I implement your code
listed below (calendar fiscal year) to make these "buckets" to total on?
Thanks
Todd
-----Original Message-----
From: pjw@... [mailto:pjw@...]
Sent: Wednesday, October 01, 2003 2:14 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Date Calc
Last Day of Last Month is = LDLM = TODAY() - DAY(TODAY)
First Day of Last Month is = FDLM = DATE(SUBSTRING(STRING(LDLM),1,3) +
STRING("01") + SUBSTRING(STRING(LDLM),6,3))
My fiscal year starts July 1st, This is for Fiscal Year Start Date:
IIF(MONTH(TODAY()) > 6, DATE("07/01/" +
SUBSTRING(STRING(TODAY()),7,2)),DATE("07/01/" +
SUBSTRING(STRING(TODAY()),7,2)) - 365)
Patrick Winter
-----Original Message-----
From: Todd Hofert [mailto:thofert@...]
Sent: Wednesday, October 01, 2003 1:09 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Date Calc
OK then, while we are on the subject. I have a dozen sales reports that I
run at the beginning of each month for the previous month. These reports
have calculations using InvoiceHead.Fiscal Year and Fiscal Period to total
sales dollars by Last Month Month to Date, Last Year Last Month Month to
Date, Year to Date through Last Month and Last Year Year to Date through
Last Month. Problem is I have to manually edit each of these dozen or so
reports each month to plug in the appropriate fiscal period. It is a hassle.
Anyone know how I can create calculations a filters that will do this
without my intervention?
Thanks
Todd Hofert
IT Director
Spartan Graphics, Inc.
-----Original Message-----
From: Podlin, Michael [mailto:michael.podlin@...]
Sent: Wednesday, October 01, 2003 1:52 PM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Report Builder Date Calc
Create two calculated fields MonthStart and MonthEnd
MonthStart:
Date_rb + 1
MonthEnd:
IIF(MONTH((Date_rb + 1)) = 12, DATE("01/01/" + STRING(YEAR(Date_rb) + 1,
"9999")), DATE(STRING(MONTH((Date_rb + 1)) + 1, ">9") + "/01/" +
STRING(YEAR(Date_rb), "9999")))
Then set your filter to quotes >= MonthStart and quotes < MonthEnd
This should work as long as it is run on the last day of the month,
otherwise you can create a long MonthStart which works off the current day
and moves it to the next month.
-----Original Message-----
From: Judy Havlik [mailto:judy.havlik@...]
Sent: Wednesday, October 01, 2003 12:40 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Report Builder Date Calc
I'm have a report that will run using the AutoRpt Utility and will pull all
quotes set to expire next month. For instance, the report will be run on
September 30, 2003 and should pull only those quotes that are set to expire
from October 1, 2003 to October 31, 2003.
Can anyone help me with the filter/calculation fields to do this? I can't
seem to get it to work properly.
Sincerely,
Judy Havlik
Plitek, L.L.C.
69 Rawls Road
Des Plaines, IL 60018
Fax: 847-827-6733
PH: 847-827-6680 x223
www.plitek.com
[Non-text portions of this message have been removed]
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/