Help Req Date for Monthly report

[David Little]

Im trying to create a sales report to analyse my customers total spend grouped in months.

Can anyone tell me the function I require to produce reports that will group my data on months

Ie: Oct01 - Nov01 - Dec01 Jan-02

I have a field delivery date which records date of delivery.


Thanks for any help offered

[Tom Cone Jr]

RE: Help Req Date for Monthly report

David,

there are several ways to do this. One approach would be

left(cdate(Your_date_type_field),6)

-- tom

[David Little]

RE: Help Req Date for Monthly report

Thanks for your help Tom, that works puts all my data in the correct order.

How do I now convert this number into Date format for my reports?

[Tom Cone Jr]

RE: Help Req Date for Monthly report

David, you cannot convert a six digit string of characters into a date. What are you asking? -- tom

[David Little]

RE: Help Req Date for Monthly report

I just want to create a report that is grouped on month & year

Ie I want the Report to display " November 2003" not numbers.

Then list all my customers sales in that month.


Thanks for your time

[Tom Cone Jr]

RE: Help Req Date for Monthly report

David,

Actually, you probably need it grouped first by year, and then by month, which is what my first expression did.

Check the functions reference for CMONTH() and CYEAR() and you will understand how this works:
"pre"
CMONTH(Your_date_field) + " " + CYEAR(Your_date_field)"/pre"

You could use this expression in a new calculated field defined in your report layout, and then drop it in your group header region.

David, Alpha Five contains a lot of useful functions for working with character strings and dates. If you're not familiar with

CDATE()
LEFT()
CMONTH()
CYEAR()

I highly recommend you spend some time looking through the functions reference. These are bread and butter functions for the Alpha Five user / developer.

-- tom

[Tom Cone Jr]

RE: Help Req Date for Monthly report

Oops! I forgot the HTML option. Let me try again.

David,

Actually, you probably need it grouped first by year, and then by month, which is what my first expression did.

Check the functions reference for CMONTH() and CYEAR() and you will understand how this works:

CMONTH(Your_date_field) + " " + CYEAR(Your_date_field)

You could use this expression in a new calculated field defined in your report layout, and then drop it in your group header region.

David, Alpha Five contains a lot of useful functions for working with character strings and dates. If you're not familiar with

CDATE()
LEFT()
CMONTH()
CYEAR()

I highly recommend you spend some time looking through the functions reference. These are bread and butter functions for the Alpha Five user / developer.

-- tom

[Barry Pannebaker]

RE: Help Req Date for Monthly report

Tom, I too am trying to group a report by year and month. In the 'Break on change in' box I have

year(Policy-"Expiration)+ month(Policy-"Expiration)

Then in the 'Group order' I have 'Order on break'.

It works, except that it lists January and February of 2004 and THEN November and December of 2003. I have the year in the expression; why doesn't it order them in year order, and then month within the year?

[Stan Mathews]

RE: Help Req Date for Monthly report

It is late in Tampa so I'll take the opportunity to reply...

Testing your expression in the interactive editor

test1 = {01/01/2004}
test2 = {02/01/2004}
test3 = {11/01/2003}
? year(test1)+ month(test1)
= 2005.000000

? year(test2)+ month(test2)
= 2006.000000

? year(test3)+ month(test3)
= 2014.000000

We see that the ordering you are experiencing is to be expected. The ordering you want could be obtained, among other ways, with....


? cyear(test1)+ str(month(test1))
= "2004 1"

? cyear(test2)+ str(month(test2))
= "2004 2"

? cyear(test3)+ str(month(test3))
= "2003 11"

This way the year controls the ordering first, then the month number, not the sum of the two.

[Stan Mathews]

RE: Help Req Date for Monthly report

Tom's expression is likely a better alternative. The display on this board is set to only show one space between "words" which makes it seem if the expression I suggested would sort incorrectly. Actually the output of the interactive window was

? cyear(test1)+ str(month(test1))
= "2004 1"

? cyear(test2)+ str(month(test2))
= "2004 2"

? cyear(test3)+ str(month(test3))
= "2003 11"

[David Little]

RE: Help Req Date for Monthly report

I�m not a computer boff, just a simple soul trying to create a simple reportthat breaks on Month & year in order .

I�m a little confused with the welcomed advice given. Could it be possible for someone to list out instructions to design the report date fields from scratch.

Ie 1, Create 2 fields -Date& Month 2, Format them to Date format 3, in report group on etc etc

That would be a real help.

[Mike Konoff]

RE: Help Req Date for Monthly report

David,

In your report, design the following calculated field:

YEAR_DATE = left(cmonth(your_invoice_date_field),3)+"-"+ltrim(str(year(your_invoice_date_field)))

result: Feb-2004

This is the field you should select as your group break.

To design a calculated field, go to menu Reports-"Calculated Fields. Type in a name (YEAR_DATE, or whatever you want to name it) and fill in the expression listed above, using the name of YOUR date field in place of your_invoice_date_field.

Go to Report-"Properties-"Detail and click on the Records tab. In the ORDER box, fill in the following:

cdate(your_invoice_date), and click OK.

It is VERY IMPORTANT that you ORDER the records on your invoice date using the cdate() function so they'll be ordered based on the following kind of result: 20040202. First 4 digits are the YEAR, the next two are MONTH, and the last two are DATE; so all records will be sorted first by YEAR, then MONTH, then DATE.

That should work. Let us know how it goes.
Mike

[Tom Cone Jr]

RE: Help Req Date for Monthly report

David,

Change your group break expression from

year(Policy->Expiration)+ month(Policy->Expiration)

to

left(cdate(Policy->Expiration),6)

and

Then in the 'Group order' continue to specify 'Order on break'.

CDATE() returns an 8 character string formatted as YYYYMMDD.

LEFT() returns a substring beginning at the leftmost character and continuing for the specified number of characters.

Left(Cdate(Policy->Expiration),6) will return YYYYMM values, and this is the sequence you probably want to sort your groups on, right?

As Stan illustrated, when you use the YEAR() and MONTH() functions they return numeric values. When you use the plus symbol between two numeric values Alpha Five adds them for you. So,

Year({04/01/2004}) returns 2004
Month({04/01/2004}) returns 4

Year({04/01/2004}) + Month({04/01/2004}) returns 2008, but
Year({12/01/2003}) + Month({12/01/2003}) returns 2015

This explains the grouping sequence in your first attempt. (2008 sorts ahead of 2015 even though April, 2004 comes after Dec, 2003).

-- tom

[David Little]

RE: Help Req Date for Monthly report

Thanks very much for taking the time to reply. I have had a play around and it's still not right.

Running out of time today. I will let you know as soon as i can make some more time up. Thanks again...........

[Barry Pannebaker]

RE: Help Req Date for Monthly report

I changed my group break expression to

left(cdate(Policy-"Expiration),6)

and it's working like I want.

My thanks to Stan for explaining how my first expression was working, and to Tom for the way to do it.