| Author |
Message |
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
   Topic: Problem with formula
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)
|
 IP Logged |
|
|
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
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)
Edited by jbalbo - 13 Feb 2013 at 4:37am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
Edited by DBlank - 13 Feb 2013 at 5:10am
|
|
IP Logged |
|
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
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)
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Feb 2013 at 5:12am |
in non-code, what are you trying to accomplish?
|
|
IP Logged |
|
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
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)
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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}))
Edited by DBlank - 13 Feb 2013 at 5:45am
|
|
IP Logged |
|
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
Posted: 13 Feb 2013 at 11:03am |
|
That is much nicer
Thanks
|
|
IP Logged |
|
jbalbo
Senior Member
Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
|
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)
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
|
|
