Need help creating formula that will run for the previous 6 months 1

[Guest_imported]

I have a date range prompt, but if no date is selected in the prompt crystal puts whatever today's date is as the start and end date. I want it to default as previous six months as start date to last day of previous month as end date. Can someone help?

 

[Naith]

Edit your parameter, and click the 'Set Default Values'.

Unattribute all your date values by clicking the << button. Enter your proposed start date, and press the > button. Then press >> to return all the previous values back into the prompt selection pool.

Ensure that your proposed date is at the top of the selection list.

Naith

 

[Ngolem]

hmmm....so you have a date range parameter that can be filled by the user or left blank to have Crystal give it a default...is that the idea?

And this is used in the record selection formula, I suppose


maybe this will work

//start date
(if isnull({?date}[1]) then
{Table.date} >= DateAdd(&quot;m&quot;,-6,
date(year(currentdate),month(currentdate),01))
else
{Table.date} >= {?date}[1]; ) and

//end date
(if isnull({?date}[2]) then
{Table.date} >= DateAdd(&quot;d&quot;,-1,
date(year(currentdate),month(currentdate,01))
else
{Table.date} >= {?date}[2]; )

this probably wouldn't be passed to the server so may be slow

Jim Broadbent

 

[mbarron]

You can accomplish this using two formulas:

@DateBegin:
datevar start;
if (minimum({?date range})= currentdate and maximum({?date range})= currentdate
then start:=date(year(dateadd(&quot;m&quot;,-6,currentdate)),month(dateadd(&quot;m&quot;,-6,currentdate)),1 else
start:=minimum({?date range}) //uses start of date range param

and

@DateEnd
datevar end;
if (minimum({?date range})= currentdate and maximum({?date range})= currentdate )
then end:=date(year(currentdate),month(currentdate),1)-1 else
end:=maximum({?date range})

For your select expert use:
{date.field} in DateBegin to DateEnd


Mike

 

[lwanalyst]

Thanks everyone for your help. Mbarron, your formula was the easiest for me to comprehend and it worked. I knew what I wanted to do, but I'm not thorough in coding in Crystal yet.

Thanks a lot!