Awkward Date Formula - Need Help 2

[lkerr]

For some reason I am just completely stumped on this one - call it lack of sleep or whatever, but its beyond me. Here's what I'm trying to do. I'm using CR9. Let me know if this isn't clear.

I need to create 3 columns that are basically subtotals for a detail section. They should be "rolling" columns based on quarters - meaning that each column should be a consecutive month. Here's some visual data:

PH1: Date Amount Month1 Month2 Month3
GH1: (Suppressed)
Detail: 10-1-05 $10.00
10-5-05 $20.00
11-10-05 $15.00
11-17-05 $24.00
12-24-05 $36.00
12-31-05 $43.00
GF1: $30.00 $39.00 $79.00

It doesn't have to be set up exactly like this, but you get the idea, I hope. As you can see, Month1 would be October, Month2 would be November, and Month3 would be December, but they would be variable fields, depending on when the report is run and the information in the report as to what they represent.

Is there a way to do this?

Hope this is clear! Thanks!

LK

 

[Madawc]

ToText({your.date}, "MMMM") for just the month-name. ToText({your.date}, "MMMM yy") for May 06 etc.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lkerr]

Well, I've gotten that far ... my problem is getting them to relate to the data. I apologize for not having said that. I need these to appear as three columns and to be next to each other in the header column, so I'm presuming I'll need to make three different formulae for the three headers. Trick is, how do I make them variable based on the data?

Also, while I'm at it, how do I do make sure the data relates to the header?

Hope this clarifies a bit.

LK

 

[lbass]

Why not insert a crosstab in the group footer, using date(print on change of month) as the column field, with no row field. Would you really still want the column headers at the top of the page if they were present in the crosstab?

-LB

 

[lkerr]

I really have made a mess out of explaining this one, haven't I? I apologize. Profusely.

The reason we were looking to set these up in columns is that the client that wants this report already has an excel spreadsheet that looks similar and we want to keep it as familiar-looking as possible.

Upon further thought, I was wondering if it would be possible to set it up using suppression formulas. What I was thinking is to group by month, then set up a sum formula, copy it across three times (one for each month) and suppress by month.

Does this sound like it would work? If anyone sees a problem, let me know ...
LK

 

[lbass]

No, you probably don't need to do that. Please explain what determines what the quarter shown is. Do you have a parameter of some type? Or does it depend on when the report is run, e.g., is it for the last complete quarter? Or the current quarter? Or?

-LB

 

[lkerr]

LB -

Theoretically, the report should only be run for one quarter's worth of data at a time. We will be pulling the report through a proprietary program that will pull the report at a "button-click." This report will be running based on data entered by the client and the data - at least up to this point - has never been entered more than 1 qtr at a time.

LK

 

[lbass]

Well, that doesn't really answer my question, but if there is somehow only one quarter's worth of data, you could use formulas like the following for the detail section:

//{@month1}:
if month({table.date}) = (datepart("q",{table.date})*3)-2 then {table.amt}

//{@month2}:
if month({table.date}) = (datepart("q",{table.date})*3)-1 then {table.amt}

//{@month3}:
if month({table.date}) = (datepart("q",{table.date})*3) then {table.amt}

Then you can right click on each formula and insert a summary at the group level.

For the month labels, you could use:

//{@month1label}:
monthname(datepart("q",{table.date})*3-2)

//{@month2label}:
monthname(datepart("q",{table.date})*3-1)

//{@month3label}:
monthname(datepart("q",{table.date})*3)

Remove the auto labels for the detail level formulas and substitute the above formulas. As long as the records are limited to one quarter in the record selection formula, this should work correctly.

-LB

 

[lkerr]

LB -

Once again, you are the man ... that fixed it just the way it should have ... WONDERFUL. And you know what, sleep-deprived or not, I probably would never have come up with that on my own.

LK