Reporting and Analytics for Engagement Cloud

Get Involved. Join the Conversation.

Topic

    Stephanie Gott
    Formulas aren't working
    Topic posted January 14, 2019 by Stephanie GottSilver Medal: 2,000+ Points, tagged Activity, Opportunity, Pipeline, Reporting and Analytics, Sales 
    74 Views, 2 Comments
    Title:
    Formulas aren't working
    Summary:
    Trying to do math to create a custom metric and formulas aren't working.
    Content:

    Hello,

    I'm trying to calculate the percentage of future dated activities on an opportunity. First, I need to calculate that using this: CASE WHEN "Activity"."Due Date"> Current_Date then COUNT(DISTINCT "Sales - CRM Pipeline"."Pipeline Facts"."# of Opportunities") else 0 end. This formula works and returns results.

    Then, I'm doing this: 100*SUM(CASE WHEN "Activity"."Due Date"> Current_Date then COUNT(DISTINCT "Sales - CRM Pipeline"."Pipeline Facts"."# of Opportunities") else 0 end)/SUM("Sales - CRM Pipeline"."Pipeline Facts"."# of Open Opportunities"). This is not erroring out but it's also returning '0' as the results. 

    I've attached screen shots where I would expect to see a result of 9%. Any thoughts? I'm really hoping someone out here familiar with the OTBI for Sales Cloud has some information. 

    Thanks,

    Stephanie

     

    Version:
    11.1.1.9.0

    Comment