[jessie Contributions: 0Level 1☆]

Business challenge – determine which customers do not have a logged phone call within the last 7 days.

I’ve crated the following formula:

(case when {call.completeddate}>(TRUNC(SYSDATE) – 7) then 1 else 0 end)

When this formula is in the results, along with {call.compoleteddate} and (TRUNC(SYSDATE) – 7), I will see the results like:

Call Date = 7/16/2015

Date – 7 = 7/21/2015

Formula = 0

then

Call Date = 7/27/2015

date – 7 = 7/21/2015

Formula = 1

Therefore, it seems like the formula is correct.

When I add the formula to Criteria | Summary as a Summary Type = Sum, field = Formula (Numeric) and Description = is 0; the results are not correct. Customer records with phone calls after 7/27/2015 are returned.

Any ideas would be great!

Bill
This is a cached copy. Click here to see the original post.

[rnedelkow Contributions: 0Level 1☆]

Another way to achieve this would be setting Criteria > Summary subtab, with the following:

Summary Type = Maximum

Field = Call Fields… Phone Call Date is not after 7 days ago

[moonparas Contributions: 0Level 1☆]

Hello jessie

You can create a Customer Saved Search and try the following:

1. Under Criteria tab > Standard subtab, add the following filter:

Activity Fields … (Joined fields) > Activity Type : Is Any Of = Phone Call

2. Under Criteria tab > Summary subtab, set the following criteria:

Summary Type = Maximum

Field = Formula (Text)

Formula = CASE WHEN max(nvl({activity.date},TO_DATE(’01/01/1900′,’MM/DD/YYYY’))) MATCH

3. Under Results tab > Columns sub tab, add the following fields:

ID (Summary Type = Group)

Company Name (Summary Type = Group)

Activity: Type (Summary Type = Group)

Activity: Date (Summary Type = Maximum)

Note: Add more Customer fields as necessary.

4. Click Save and Run.

This should show the Customers without Phone Call records within 7 days, and the last created Phone Call Record. I hope this helps.

[jessie Contributions: 0Level 1☆]

@rnedelkow Thank you for the reply, I was able to get this method to work.

[jessie Contributions: 0Level 1☆]

@moonparas Thank you for your reply. I was not able to get this to work.

To test / learn, I placed CASE WHEN max(nvl({activity.date},TO_DATE(’01/01/1900′,’MM/DD/YYYY’))) MATCH works. I would have thought it should be Is = MATCH.

Would you be able to provide a suggestion on what I might be doing wrong?

[moonparas Contributions: 0Level 1☆]

Hi Jessie, sorry for the confusion,,, should be Is = MATCH

[Author]

Posts