7 Replies Latest reply on Dec 22, 2016 5:50 AM by Mahfooj Khan

    How to compare Opportunity revenue with the actual sales from another data source?

    Alex Lin

      Hi,

       

      I'm getting Opportunity and Actual sales from two data sources. I tried to look up the actual sales table to see how much the opportunity revenue has been realized after the opportunity was closed.

       

      Below are the tables for opportunity and actual sales examples:

       

      1. Opportunity

          

      Customer IDProduct IDClose DateRevenue
      A12310/25/2016100000

       

      2. Actual Sales

          

      Customer IDProduct IDSales DateSales Amount
      A1238/19/20165000
      A12310/30/201615000
      A12311/25/201628000
      A12312/10/201610000

       

      In the above example, the results I'm looking for is the total sales amount from Oct to Dec.

       

                    

      Customer   IDProduct IDClose Yearmonth

      Opp

      Revenue

      Realized Sales Amount
      A123201610

      100000

      53000

                    

      Customer   IDProduct IDClose DateRevenue
      A12310/25/2016100000

       

      Below is the calculated field I used to get the result:

       

      IF ATTR( [Close YearMonth] )<= ATTR([Sales (New Biz Realization)].[Sales YearMonth])

      THEN SUM([Sales (New Biz Realization)].[Sales Amount])

      ELSE 0

      END

       

      But the result is incorrect.

       

      Does anyone know how to solve this?

       

      Thanks,

      Alex