Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula Logic Problem

Status
Not open for further replies.

skaestner

Technical User
Apr 21, 2009
35
US
Using CR 2008

OK here is the desired result:

Total 7 days Total 14 days
John Jones 1 0
Mike Smith 0 1


What's currently happening


Total 7 days Total 14 days
John Jones 6 0
Mike Smith 0 9

here are the formula's:

formula Last Contact Date:

if {BANNER_DAR.LAST_CONTACT} <> "Conversion" then
right({BANNER_DAR.LAST_CONTACT}, 19)

formula ToDate Last Contact:

Date({@Last Contact Date})

formula 7 Day Count:

if (datediff("d", {@ToDate Last Contact}, CurrentDate) >= 7 and
datediff("d", {@ToDate Last Contact}, CurrentDate) <= 13) then 1 else 0;

formula 14 Day Count:
if datediff("d", {@ToDate Last Contact}, CurrentDate) >= 14 then 1 else 0;

the display formulas
Total 7 Day
WhilePrintingRecords;
sum({@7 Day Count}, {BANNER_DAR.SPR_NAME})

Total 14 Day
WhilePrintingRecords;
sum({@14 Day Count}, {BANNER_DAR.SPR_NAME})



so the problem that I am getting is that 7 Day Count is looping and it should not be looping as it should only give a 1 and then move on to second record. Any ideas?

 
YOur data would indicate that there have been 6 actual contacts with John Jones in last seven days but you only want to show that as a single contact?

If that is the case then move your formula into SPR_NAME group footer and cahnge to

if (datediff("d", Maximum({@ToDate Last Contact},{BANNER_DAR.SPR_NAME}), CurrentDate) >= 7 and
datediff("d", maximum({@ToDate Last Contact},{BANNER_DAR.SPR_NAME}), CurrentDate) <= 13) then 1 else 0;

Do same with 14 day formula. However, you will not be able to summarise these formula in report footer, you will need to use a running total with the same condition for evauluating the count.

Ian


 
I am trying to find out whether the last contact was between 7 and 14 days or 14 days or greater. If it was 7-13 then display 1 ... if it was 14 or more then display 1. however it appears that the formulas are looping, i have placed them both in the report footer and various other places but to no luck am getting the same results.

for instance:

Last Contact 7 Day 14 Day
Melissa Hart 6/25/2009 1 0
Jon Jacobs 6/20/2009 0 1

Mike Jones is responsible for both
so he would have a total:

Total 7 Day Total 14 Day
Mike Jones 1 1
 
Please explain how you made this work so that the thread becomes useful to others.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top