Formula Logic Problem

[skaestner]

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?

 

[IanWaterman]

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

 

[skaestner]

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

 

[skaestner]

thanks but figured it out!!

 

[lbass]

Please explain how you made this work so that the thread becomes useful to others.

-LB