Forums › Cached › Customization › Searches / List Views

This topic contains 5 replies, has 0 voices, and was last updated by moonparas 9 years, 3 months ago.

  • Author
    Posts
  • July 28, 2015 at 1:38 pm #5507 Score: 0

    jessie
    • Contributions: 0
    • Level 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.

  • July 28, 2015 at 2:29 pm #5508 Score: 0

    rnedelkow
    • Contributions: 0
    • Level 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

  • July 28, 2015 at 3:19 pm #5509 Score: 0

    moonparas
    • Contributions: 0
    • Level 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.

  • August 3, 2015 at 9:18 am #5510 Score: 0

    jessie
    • Contributions: 0
    • Level 1
    • ☆

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

  • August 3, 2015 at 9:21 am #5511 Score: 0

    jessie
    • Contributions: 0
    • Level 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?

  • August 3, 2015 at 10:32 am #5512 Score: 0

    moonparas
    • Contributions: 0
    • Level 1
    • ☆

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

  • Author
    Posts

You must be logged in to reply to this topic.