Grouping on Formula Field

[sbrunswik]

I am trying to create a graph based on daily census in a time frame. I have a report that caluclates current census. I am trying to create a group that allow me to calucalate census for each day of the month. I have the following formulas. I am using Crystal 10 dev. Any help would be greatly appreciated.

FirstDayMonth

DateVar AccumDate;
AccumDate :=DateSerial(Year(CurrentDate),Month(CurrentDate),1);
AccumDate;

AccDate

DateVar AccumDate;
DateAdd ("d",1,AccumDate);
AccumDate

However I do not see either formula field when I try and create a new group.

Thanks
sbrunswik

 

[lbass]

There is nothing to group on here. Your first formula returns the first day of the month, and your second formula returns the second day of the month.

You need to use database fields in your grouping formula. You should just be able to group on {table.date}. You can limit the data in your record selection formula to this month's data, but using the following:

{table.date} in dateserial(year(currentdate),month(currentdate),01) to dateserial(year(currentdate),month(currentdate)+1,01)-1

-LB

 

[sbrunswik]

Thanks for your help however maybe I was unclear I want to be able to create a graph (group) that shows census on a daily basis my datatable has

clientid 1 2 4
LOC A A C
BegDate 1/1/2005 1/15/2005 1/20/2005
EndDate 3/1/2005 1/19/2005

I need on a daily basis in a time frame the number of client id's active for a specific LOC. I need to know that one
1/1 census 1
1/2 census 1
1/3 census 1
1/15 census 2
1/20 census 2

does this make sense???

thks again

 

[lbass]

I think you would have to make a series of formulas, one for each census day, as in:

//{@1/1/05):
if {table.enddate} >= date(2005,01,01) and
{table.startdate} <= date(2005,01,01) then 1 else 0

//{@1/2/05}:
if {table.enddate} >= date(2005,01,02) and
{table.startdate} <= date(2005,01,02) then 1 else 0

If you wanted to avoid hard dates, you could use formulas like the following instead:

//{@firstdaylastmonth}:
if {table.enddate} >= dateserial(year(currentdate),month(currentdate)-1,01) and
{table.startdate} <= dateserial(year(currentdate),month(currentdate)-1,01) then 1 else 0

//{@seconddaylastmo}:
if {table.enddate} >= dateserial(year(currentdate),month(currentdate)-1,02) and
{table.startdate} <= dateserial(year(currentdate),month(currentdate)-1,02) then 1 else 0

For your chart, you would use {table.location} as your "on change of" field, and then add a formula for each census day you want to show on the chart using a sum as the summary.

-LB