Technical Questions: Problem with formula

Print Page | Close Window

Problem with formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19097
Printed Date: 07 May 2024 at 1:38pm

Topic: Problem with formula

Posted By: jbalbo
Subject: Problem with formula
Date Posted: 13 Feb 2013 at 3:58am

Hi,

Having trouble with the following formula:
Not sure if I can even do this

Local Datevar Start;
Local Datevar End;

If ({APPOINTMENT.EffDate} <= {?date}-27 And {APPOINTMENT.Expdate} > {?date}-27)
Then
Start = {?date}-27
End = {?date}-21

else

if ({APPOINTMENT.EffDate} > {?date}-27 and {APPOINTMENT.Expdate}>= {?date}-21)
then
start = cdate({APPOINTMENT.EffDate}
end = {?date}-21

else

if ({APPOINTMENT.EffDate} < = {?date}-27 and {APPOINTMENT.Expdate} > {?date}-21)
then
start = {?date}-27
end = {?date}-21

Datediff(Start, End)

Replies:

Posted By: jbalbo
Date Posted: 13 Feb 2013 at 4:36am

so I think I straingtend out the formaul
but getting nothing ???

commeted out datedif and displayed start and get blanks

//
Local Datevar Start;
Local Datevar End;

If ({APPOINTMENT.EffDate} <= {?date}-27 And {APPOINTMENT.Expdate} in {?date}-27 to {?date}-21)
Then
Start = {?date}-27
else
if ({APPOINTMENT.EffDate} < {?date}-27 and {APPOINTMENT.Expdate}> {?date}-21)
then
start = {?date}-27
else
if ({APPOINTMENT.EffDate} <= {?date}-21 and {APPOINTMENT.Expdate} > {?date}-21)
then
start = {APPOINTMENT.EffDate} ;

// end

If ({APPOINTMENT.EffDate} <= {?date}-27 And {APPOINTMENT.Expdate} in {?date}-27 to {?date}-21)
Then
End = {APPOINTMENT.Expdate}
else
if ({APPOINTMENT.EffDate} < {?date}-27 and {APPOINTMENT.Expdate}> {?date}-21)
then
end = {?date}-21
else
if ({APPOINTMENT.EffDate} > {?date}-21 and {APPOINTMENT.Expdate} > {?date}-21)
then
end = {?date}-21;
start
//Datediff("d",Start, End)

Posted By: DBlank
Date Posted: 13 Feb 2013 at 4:38am

it is possible but you would need to change it around a bit.

That being said, if-then 1 is duplicative of if-then 3 and both return a value of 6 all the time. The second if-then is the only dynamic value.

is that what you want?

EDIT: this is in reference to your first post

Posted By: jbalbo
Date Posted: 13 Feb 2013 at 4:58am

Here is my newest creation!!!

it seems The logic is what I need a sample would be

HArry..

appointment.Effdate = 11/30/2012 12:00:00AM
appointment.expdate = 1/8/2013 12:00:00AM
date = 1/18/13
so... date -27 = 12/22/2012
date-21 = 12/28/2012

I would think the second part of the if stement would work?

But I get nothing is it the nme of the variables?

//
Local Datevar Start;
Local Datevar End;

If (cdate({APPOINTMENT.EffDate}) <= {?date}-27 And cdate({APPOINTMENT.Expdate}) in {?date}-27 to {?date}-21)
Then
Start = {?date}-27
else
if (cdate({APPOINTMENT.EffDate}) < {?date}-27 and cdate({APPOINTMENT.Expdate})> {?date}-21)
then
start = {?date}-27
else
if (cdate({APPOINTMENT.EffDate}) in {?date}-27 to {?date}-21 and cdate({APPOINTMENT.Expdate}) > {?date}-21)
then
start = cdate({APPOINTMENT.EffDate}) ;

// end

If ({APPOINTMENT.EffDate} <= {?date}-27 And {APPOINTMENT.Expdate} in {?date}-27 to {?date}-21)
Then
End = {APPOINTMENT.Expdate}
else
if ({APPOINTMENT.EffDate} < {?date}-27 and {APPOINTMENT.Expdate}> {?date}-21)
then
end = {?date}-21
else
if (cdate({APPOINTMENT.EffDate}) in {?date}-27 to {?date}-21 and cdate({APPOINTMENT.Expdate}) > {?date}-21)then
end = {?date}-21;

start-1

//Datediff("d",Start, End)

Posted By: DBlank
Date Posted: 13 Feb 2013 at 5:12am

in non-code, what are you trying to accomplish?

Posted By: jbalbo
Date Posted: 13 Feb 2013 at 5:29am

Hi D..

OK I found what I was missing..

The :

Start:=

if you curious I am trying to find the date diff bewteen two dates(apptment start and appointment end) on these conditions...

User eneter a date I need to see where the the appointment range begins and ends.. four weeks prior , if the appoint started bewteen those dates then I use the apptment start date , if it started before that date then I use the start date(Input date - 28) same logic for end date if the appointment ended during the appointment date range then use the appointment end date if it ended after the week (date-21) then use thedate-21 ..

THanks for the help...

Joe

I think I'll be back with more of this today :)

//
Local Datevar Start;
Local Datevar End;

If (cdate({APPOINTMENT.EffDate}) <= {?date}-27 And cdate({APPOINTMENT.Expdate}) in {?date}-27 to {?date}-21)
Then Start:={?date}-28
else
if (cdate({APPOINTMENT.EffDate}) < {?date}-27 and cdate({APPOINTMENT.Expdate})> {?date}-21)
then start:={?date}-28
else
if (cdate({APPOINTMENT.EffDate}) in {?date}-27 to {?date}-21 and cdate({APPOINTMENT.Expdate}) > {?date}-21)
then
start:=cdate({APPOINTMENT.EffDate}) ;

// end

If ({APPOINTMENT.EffDate} <= {?date}-27 And {APPOINTMENT.Expdate} in {?date}-27 to {?date}-21)
Then
End:=cdate({APPOINTMENT.Expdate})
else
if ({APPOINTMENT.EffDate} < {?date}-27 and {APPOINTMENT.Expdate}> {?date}-21)
then
end:={?date}-21
else
if (cdate({APPOINTMENT.EffDate}) in {?date}-27 to {?date}-21 and cdate({APPOINTMENT.Expdate}) > {?date}-21)then
end:={?date}-21;

//tart:=end

Datediff("d",Start, End)

Posted By: DBlank
Date Posted: 13 Feb 2013 at 5:45am

glad you sorted it out.

based on your description I think you could simplify a little if you wanted

datediff('d',(If ({APPOINTMENT.EffDate} <= {?date}-27 then {?date}-27 else {APPOINTMENT.EffDate}), (if {APPOINTMENT.EffDate}> {?date}-21 then {?date}-21 else {APPOINTMENT.EffDate}))

Posted By: jbalbo
Date Posted: 13 Feb 2013 at 11:03am

That is much nicer

Thanks

Posted By: jbalbo
Date Posted: 13 Feb 2013 at 11:46am

Unfortunatly as I tested it changed abit:
This is the final logic and I've tested
do you think there is an datedif I can use for this ?

//
Local Datevar Start;
Local Datevar End;

If (cdate({APPOINTMENT.EffDate}) <= {?date}-27 And cdate({APPOINTMENT.Expdate}) in {?date}-27 to {?date}-21)
Then Start:={?date}-28
else
if (cdate({APPOINTMENT.EffDate}) < {?date}-27 and cdate({APPOINTMENT.Expdate})> {?date}-21)
then start:={?date}-28
else
if (cdate({APPOINTMENT.EffDate}) in {?date}-27 to {?date}-21)
then
start:=cdate({APPOINTMENT.EffDate}-1) ;

// end

If ({APPOINTMENT.EffDate} <= {?date}-27 And {APPOINTMENT.Expdate} in {?date}-27 to {?date}-21)
Then
End:=cdate({APPOINTMENT.Expdate})
else
if ({APPOINTMENT.EffDate} < {?date}-27 and {APPOINTMENT.Expdate}> {?date}-21)
then
end:={?date}-21
else
if (cdate({APPOINTMENT.EffDate}) in {?date}-27 to {?date}-21 and cdate({APPOINTMENT.Expdate}) > {?date}-21)then
end:={?date}-21;

//tart:=end

Datediff("d",Start, End)

Posted By: DBlank
Date Posted: 13 Feb 2013 at 12:17pm

possibly but given the new conditions I am not sure it would be any less complicated. If your formual is working I think you should be fine.

Just be aware that you might have records that do not meet any conditions and you did not give a final else for either your start or end.

I think these will return a 0

Print Page | Close Window