Last 6 months by Month 1

[Jcfx]

CR 9.2 on a Sql data base. Two detail sections. Section a has part number, description, chargedate, and qtyrcvd. Section b contains the following formulas, the formulas are then sum'd in the group footer. The Detail sections are suppressed. There are no parameter fields. Everything is based off of current date.

Output expected is as follows:
Partid desc Current L12M LFY CM-1 CM-2 CM-3 CM-4 CM-5 CM-6
xxxxx Blah 10 240 0 75 85 80 0 0 0

If the current month is October, then CM-1 is September, CM-2 is August etc.

I have Current working correctly, Last 12 months working correctly <L12M>, Last Fiscal year working correctly <LFY>

and CM-1 working correctly using LastfullMonth in comparison. After that it falls a part. The formula I am using for CM-2 through CM-6 is as follows, incrementing the -1 as necessary:

//this example would be for August
if {ORDERLN.CHGDATE} in dateadd("m",-1,maximum(lastfullmonth)) to minimum(lastfullmonth)-1 then {ORDERLN.QTYREQD} else 0

//this example would be for July
if {ORDERLN.CHGDATE} in dateadd("m",-2,maximum(lastfullmonth)) to minimum(lastfullmonth)-2 then {ORDERLN.QTYREQD} else 0

Again, these are placed in detail <b> and summed in the Partid group footer.

Our database is only 4 months old, so data should only be showing for (using October for the current date) September,August,July with June,May and April being 0.

I must be missing something simple. My column for august is all zero, with my column for July actually being what August should be, June, May and April are duplicating July.

Sorry if this is too much information or too confusing. I have been reading these boards trying to glean as much as I can from old posts, but I've farked something up somewhere.





Julie Cox
CR Pro Version 9.2

 

[kskid]

I use the dateserial function in my monthly reports


//Previous Month (September)
if {ORDERLN.CHGDATE} in
dateserial(year(currentdate),month(currentdate)-1,1)
to_
dateserial(year(currentdate),month(currentdate),1)
then {ORDERLN.QTYREQD} else 0


//Previous Month (August)
if {ORDERLN.CHGDATE} in
dateserial(year(currentdate),month(currentdate)-2,1)
to_
dateserial(year(currentdate),month(currentdate)-1,1)
then {ORDERLN.QTYREQD} else 0

Note that I am using to_ which means excluding this date.
Otherwise, it would be

to
dateserial(year(currentdate),month(currentdate)-1,1-1)

 

[synapsevampire]

You can also try:

//this example would be for August
whileprintingrecords;
datevar CurrMonthStart:= cdate(year(currentdate),month(currentdate),1);
if {ORDERLN.CHGDATE} >= dateadd("m",-1,CurrMonthStart)
and
{ORDERLN.CHGDATE} < CurrMonthStart then
...

//this example would be for July
whileprintingrecords;
datevar CurrMonthStart:= cdate(year(currentdate),month(currentdate),1);
if {ORDERLN.CHGDATE} >= dateadd("m",-2,CurrMonthStart)
and
{ORDERLN.CHGDATE} < dateadd("m",-1,CurrMonthStart) then
...

I think that you'll find that the SQL passes readily and that it's easier to maintain as well.

I've used dateserial before too, but I had some hiccups with SQL passing in some instances.

-k

 

[lbass]

Or, change your formulas to the following syntax, e.g., for July:

if {ORDERLN.CHGDATE} in dateadd("m",-2,minimum(lastfullmonth)) to dateadd("m",-2,maximum(lastfullmonth)) then{ORDERLN.QTYREQD} else 0

-LB

 

[synapsevampire]

Don't use the maximum(lastfullmonth)

Check what's being returned, The months max date was 30, so you'll get 30 for the month before (August), instead of 31.

-k

 

[lbass]

SV is correct--you shouldn't use maximum(lastfullmonth)--I wasn't paying close enough attention.

-LB

 

[Jcfx]

Thank you very much, I used a variation of SV's code.

I am really glad I found these forums. I've learned more about date formula's here just reading back through posts, then in the two crystal referance volumes that I have.

Thanks alot folks.



Julie Cox
CR Pro Version 9.2