Date/Month Calculation

Yahoo Archive
1
In crystal you can use MONTHNAME(TABLE.FIELD) for a formula to give the name of a month. Then you can Group by the Formula. Cross-Tabs will allow you to put the months in columns.


Troy Funte
Liberty Electronics

----- Original Message -----
From: Wendy Bowen
To: vantage@yahoogroups.com
Sent: Tuesday, November 05, 2002 7:06 AM
Subject: [Vantage] Date/Month Calculation


Hi everyone!

I would like to display some sales order $$'s on a report based on the months that the jobs are due.

For example:

Job January February March April
02561 $8974
08547 $89,745.12
03678 $7845.23

How do I take the Jobhead.ReqDueDate and covert it to a month name so I can sort it in the appropriate column to pull in the sales order $$$'s?

I've been playing around with IF statements but I'm not sure if I'm on track with this one.

Thanks!

Wendy Bowen
Network Administrator
Schmald Tool & Die, Inc.
Burton, Michigan


[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor
ADVERTISEMENT




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 the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]
2
Hi everyone!

I would like to display some sales order $$'s on a report based on the months that the jobs are due.

For example:

Job January February March April
02561 $8974
08547 $89,745.12
03678 $7845.23

How do I take the Jobhead.ReqDueDate and covert it to a month name so I can sort it in the appropriate column to pull in the sales order $$$'s?

I've been playing around with IF statements but I'm not sure if I'm on track with this one.

Thanks!

Wendy Bowen
Network Administrator
Schmald Tool & Die, Inc.
Burton, Michigan


[Non-text portions of this message have been removed]
3
I wouldn't use month name in the formula but rather month value. Similar
invoices sales reports I've done I use something like:
JanJobValue = IIF(MONTH(JobHead.RegDueDate) = 1,JobValue, 0)
FebJobValue = IIF(MONTH(JobHead.RegDueDate) = 2,JobValue, 0)
etc....
Then at the appropriate report group footer level print each of the values
across (or print an aggregate of the job value).
-Todd C.


-----Original Message-----
From: Wendy Bowen [mailto:wpursche@...]
Sent: Tuesday, November 05, 2002 9:06 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Date/Month Calculation


Hi everyone!

I would like to display some sales order $$'s on a report based on the
months that the jobs are due.

For example:

Job January February March April
02561 $8974
08547 $89,745.12
03678 $7845.23

How do I take the Jobhead.ReqDueDate and covert it to a month name so I can
sort it in the appropriate column to pull in the sales order $$$'s?

I've been playing around with IF statements but I'm not sure if I'm on track
with this one.

Thanks!

Wendy Bowen
Network Administrator
Schmald Tool & Die, Inc.
Burton, Michigan


[Non-text portions of this message have been removed]



Yahoo! Groups Sponsor

ADVERTISEMENT

<http://rd.yahoo.com/M=237459.2482214.3917349.2146399/D=egroupweb/S=17050071
83:HM/A=1267611/R=0/*http://ad.doubleclick.net/jump/N2524.Yahoo/B1071650;sz=
300x250;ord=1036508846054895?>

<http://us.adserver.yahoo.com/l?M=237459.2482214.3917349.2146399/D=egroupmai
l/S=:HM/A=1267611/rand=399574648>

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/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<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
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
4
When building a month-by-column report another consideration is
fiscal vs calendar month. A Fiscal view, if yours is different than
calendar, may require hard-coding the begin and end dates for each
column (maybe a report for 2002, 2003...). There is a Fiscal table
in Vantage, but difficult to link with this type of report.

A calendar month view is a little easier. As Todd stated, both
require buckets and aggregates. If your looking for ideas I've a
snapshot I'll send your way.

Good luck,
Lonnie


--- In vantage@y..., Todd Caughey <caugheyt@h...> wrote:
> I wouldn't use month name in the formula but rather month value.
Similar
> invoices sales reports I've done I use something like:
> JanJobValue = IIF(MONTH(JobHead.RegDueDate) = 1,JobValue, 0)
> FebJobValue = IIF(MONTH(JobHead.RegDueDate) = 2,JobValue, 0)
> etc....
> Then at the appropriate report group footer level print each of the
values
> across (or print an aggregate of the job value).
> -Todd C.
>
>
> -----Original Message-----
> From: Wendy Bowen [mailto:wpursche@s...]
> Sent: Tuesday, November 05, 2002 9:06 AM
> To: vantage@y...
> Subject: [Vantage] Date/Month Calculation
>
>
> Hi everyone!
>
> I would like to display some sales order $$'s on a report based on
the
> months that the jobs are due.
>
> For example:
>
> Job January February March April
> 02561 $8974
> 08547 $89,745.12
> 03678 $7845.23
>
> How do I take the Jobhead.ReqDueDate and covert it to a month name
so I can
> sort it in the appropriate column to pull in the sales order $$$'s?
>
> I've been playing around with IF statements but I'm not sure if I'm
on track
> with this one.
>
> Thanks!
>
> Wendy Bowen
> Network Administrator
> Schmald Tool & Die, Inc.
> Burton, Michigan
>
>
> [Non-text portions of this message have been removed]
>
>
>
> Yahoo! Groups Sponsor
>
> ADVERTISEMENT
>
>
<http://rd.yahoo.com/M=237459.2482214.3917349.2146399/D=egroupweb/S=17
050071
>
83:HM/A=1267611/R=0/*http://ad.doubleclick.net/jump/N2524.Yahoo/B10716
50;sz=
> 300x250;ord=1036508846054895?>
>
> <http://us.adserver.yahoo.com/l?
M=237459.2482214.3917349.2146399/D=egroupmai
> l/S=:HM/A=1267611/rand=399574648>
>
> 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/.
> <http://groups.yahoo.com/group/vantage/files/.>
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> <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
> <http://groups.yahoo.com/group/vantage/links>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/> .
>
>
>
>
> [Non-text portions of this message have been removed]
5
Todd,

Good idea! So far it's working well and this type of formula isn't taking as long to run.

Thanks!

~Wendy
----- Original Message -----
From: Todd Caughey
To: 'vantage@yahoogroups.com'
Sent: Tuesday, November 05, 2002 10:26 AM
Subject: RE: [Vantage] Date/Month Calculation


I wouldn't use month name in the formula but rather month value. Similar
invoices sales reports I've done I use something like:
JanJobValue = IIF(MONTH(JobHead.RegDueDate) = 1,JobValue, 0)
FebJobValue = IIF(MONTH(JobHead.RegDueDate) = 2,JobValue, 0)
etc....
Then at the appropriate report group footer level print each of the values
across (or print an aggregate of the job value).
-Todd C.


-----Original Message-----
From: Wendy Bowen [mailto:wpursche@...]
Sent: Tuesday, November 05, 2002 9:06 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Date/Month Calculation


Hi everyone!

I would like to display some sales order $$'s on a report based on the
months that the jobs are due.

For example:

Job January February March April
02561 $8974
08547 $89,745.12
03678 $7845.23

How do I take the Jobhead.ReqDueDate and covert it to a month name so I can
sort it in the appropriate column to pull in the sales order $$$'s?

I've been playing around with IF statements but I'm not sure if I'm on track
with this one.

Thanks!

Wendy Bowen
Network Administrator
Schmald Tool & Die, Inc.
Burton, Michigan

[Non-text portions of this message have been removed]