Dates 6 Months Forward and Backward from Current Date 1

[blueboyz]

Crystal Reports v2008
Quantum 2009/Pervasive SQL v10

I need to write a report to show inventory quantities available currently as well as 6 months forward and 6 months past. For example, if my current date is Oct. 7 the report would show:

Apr 7
May7
Jun 7
Jul 7
Aug 7
Sept 7
Oct 7
Nov 7
Dec 7
Jan 7
Feb 7
Mar 7
Apr 7

(The dates would actually be the columns and not the rows.)

There is a formula for the quantity on hand {@Quantity}.

How do I write a formula to display the quantities for inventory that is 6 months before and 6 months after the current date?

WhilePrintingRecords;
DateVar Column1 := DateAdd("m", -6,{@ReportDate});
DateVar Column2 := DateAdd("m", -5,{@ReportDate});
DateVar Column3 := DateAdd("m", -4,{@ReportDate});
DateVar Column4 := DateAdd("m", -3,{@ReportDate});
DateVar Column5 := DateAdd("m", -2,{@ReportDate});
DateVar Column6 := DateAdd("m", -1,{@ReportDate});
DateVar Column8 := DateAdd("m", +1,{@ReportDate});
DateVar Column9 := DateAdd("m", +2,{@ReportDate});
DateVar Column10 := DateAdd("m", +3,{@ReportDate});
DateVar Column11 := DateAdd("m", +4,{@ReportDate});
DateVar Column12 := DateAdd("m", +5,{@ReportDate});
DateVar Column13 := DateAdd("m", +6,{@ReportDate});
//Column8 is the current month and day

{@ReportDate} = Today

The formula for the columns gives me an error that "A date is required here" and highlights
DateAdd("m", -6,{@ReportDate})

 

[lbass]

You will have to show the content of your formulas. It sounds like {@ReportDate} is not a date, but perhaps a datetime?

-LB

 

[blueboyz]

{@ReportDate} = Today

{@Quantity} =
GetPeachQtyOnHandv2 (Filename, {LineItem.ItemRecordNumber})

 

[BeeBear]

Hi,

Dont forget to add in a calculation for where TODAY = 31st of the month, and 6 months ago has 30 days only (eg October 31st - 6 months = 31st April - Your report would error as there is no 31 April).

You would also have to allow for February (including leap year adjustments). Otherwise your report could error every time its run any time after the 28th of the month.

I can't tell you the formula off the top off my head, but will add it to this from work tomorrow if you need it.

regards

BB

*** Count your blessings not your problems******

 

[lbass]

All you need to do is wrap each dateadd() in date() and the formula will execute and will also adjust for February, leap years, etc. However, I think you could more simply insert a crosstab and use a formula like the following for your column field:

if {table.date} in dateadd("m",-6,currentdate) to dateadd("m",-5,currentdate)-1 then
dateadd("m",-6,currentdate) else //or "Six months ago"
if {table.date} in dateadd("m",-5,currentdate) to dateadd("m",-4,currentdate)-1 then
dateadd("m",-5,currentdate) else //or "Five months ago"
if {table.date} in dateadd("m",-4,currentdate) to dateadd("m",-3,currentdate)-1 then
dateadd("m",-4,currentdate) else //or "Four months ago"
//etc.

Add the quantity field as your summary field.

-LB

 

[blueboyz]

Thanks LB. I'll give your suggestion a try.

 

[blueboyz]

I'm still getting an error. I attached a screenshot. The error says that A number, currency amount, boolean,...is expected here. The word "curentdate" is highlighted. Do I need to put parentheses around currentdate?

 

[blueboyz]

I forgot the attachment of the screenshot. If I put a parentheses around "currentdate", I get another error message that the parentheses )is missing and "currentdate" is highlighted and the cursor is at the end of "currentdate".

Here is how my formula looks:

If {JrnlRow.RowDate} in DateAdd("m",-6,currentdate)to dateadd("m",-5,currentdate)-1 then
dateadd("m",-6,(curentdate))else //or "six months ago"
if {JrnlRow.RowDate} in DateAdd("m",-5,currentdate)to dateadd("m",-4,currentdate)-1 then
dateadd("m",-5,curentdate)else //or "five months ago"
if {JrnlRow.RowDate} in DateAdd("m",-4,currentdate)to dateadd("m",-3,currentdate)-1 then
dateadd("m",-4,curentdate)else //or "four months ago"
if {JrnlRow.RowDate} in DateAdd("m",-3,currentdate)to dateadd("m",-2,currentdate)-1 then
dateadd("m",-3,curentdate)else //or "three months ago"
if {JrnlRow.RowDate} in DateAdd("m",-2,currentdate)to dateadd("m",-1,currentdate)-1 then
dateadd("m",-2,curentdate)else //or "two months ago"
if {JrnlRow.RowDate} in DateAdd("m",-1,currentdate)to dateadd("m",currentdate)-1 then
dateadd("m",-1,curentdate)else //or "one month ago"
if {JrnlRow.RowDate} in DateAdd("m",currentdate)to dateadd("m",+1,currentdate)+1 then
dateadd("m",curentdate)else //or "current month"
if {JrnlRow.RowDate} in DateAdd("m",+1,currentdate)to dateadd("m",+2,currentdate)+1 then
dateadd("m",+1,curentdate)else //or "next month"
if {JrnlRow.RowDate} in DateAdd("m",+2,currentdate)to dateadd("m",+3,currentdate)+1 then
dateadd("m",+2,curentdate)else //or "two months ahead"
if {JrnlRow.RowDate} in DateAdd("m",+3,currentdate)to dateadd("m",+4,currentdate)+1 then
dateadd("m",+3,curentdate)else //or "three months ahead"
if {JrnlRow.RowDate} in DateAdd("m",+4,currentdate)to dateadd("m",+5,currentdate)+1 then
dateadd("m",+4,curentdate)else //or "four months ahead"
if {JrnlRow.RowDate} in DateAdd("m",+5,currentdate)to dateadd("m",+6,currentdate)+1 then
dateadd("m",+5,curentdate)else //or "five months ahead"
if {JrnlRow.RowDate} in DateAdd("m",+6,currentdate)to dateadd("m",+7,currentdate)+1 then
dateadd("m",+6,curentdate)else //or "six months ahead"

 

[lbass]

You are spelling currentdate wrong in some places--it needs two "r's".

-LB

 

[blueboyz]

I need to understand the date formula posted above. After correcting the spelling of "currentdate" I was able to get a cross tab report with the months as columns on the report.

My formulas for the quantities are:
{@Sold} =
If {JrnlHdr.JrnlKey_Journal} = 3 then {JrnlRow.Quantity}

{@Orders} =
If {JrnlHdr.JrnlKey_Journal} = 11 then {JrnlRow.Quantity}

{@POs} =
If {JrnlHdr.JrnlKey_Journal} = 10 then {JrnlRow.Quantity}

There is a problem with the data not posting in the correct month.

Here is what it is displaying:

Total Dec-08 Jan-09 Feb-09 Mar-09
Sold 3 1 0 2 0
Orders 2 0 0 0 2
POs 11 10 0 1 0

It should show:

Total Dec-08 Jan-09 Feb-09 Mar-09
Sold 3 0 1 0 2
Orders 2 0 0 0 2
POs 11 0 10 0 1

(Sold: 1 on 1/21/09; 2 on 3/1/09
Orders 2 on 3/31/09
POs 10 on 1/2/09; 1 on 3/26/09)

Does the formula I'm using for the {@Dates} above start calculating what the month is from the current date?

How can I correct my formula so the quantities fall in the correct monthly period?

 

[lbass]

In your crosstab, you seem to be basing columns on calendar months, while previously you indicated that by previous month you meant the month prior to the currentdate, which could be mid-month, and therefore cross months, e.g., September 29 - October 29. Do you want the crosstab to be based on the six months before the beginning of the current month, e.g., if today is October 30, would you want the previous six months to be April to September, and the next six months to be October to March? In other words the year would be April to March, based on the currentdate. Or would you want it to be based on May to April?

-LB

 

[blueboyz]

If today is Oct. 30, 2008, then I need the columns for the six months prior to Oct., Oct. and the next six months:

April 2008
May 2008
June 2008
July 2008
August 2008
September 2008
October 2008
November 2008
December 2008
January 2009
February 2009
March 2009
April 2009

 

[lbass]

Then use a record selection formula like this:

{JrnlRow.RowDate} in DateAdd("m",-6,currentdate-day(currentdate)+1) to dateadd("m",+7,currentdate-day(currentdate)+1)-1

In your crosstab, add {JrnlRow.RowDate} as your column field, and while it is highlighted->group options->choose "on change of month".

-LB