3 Replies Latest reply on Apr 4, 2018 9:23 PM by lalitha p

    Track the movements

    lalitha p

      Hello Everyone,

       

        I realized that i need some assistance in developing a report. I have a requirement something like this.

        I have a source with all the information along with a month field.

        Month      Opportunity    Stage     Value 

        Nov - 17       1001             W          100

        Nov - 17       1002             W           70

        Nov - 17       1003             B           150

        Nov - 17       1004             S            30

        Nov - 17       1005             E            60

       

        Dec - 17       1001             E          100

        Dec - 17       1002             S           70    --- New Sold

        Dec - 17       1003             S           150  --- New Sold

        Dec - 17       1004             S            30  ---- Prev Sold

        Dec - 17       1005             E            60

        Dec - 17       1006             W           90

       

        Jan - 18        1001            E          100

        Jan - 18        1002            S           70     --- Prev Sold

        Jan - 18        1003            S          150    --- Prev Sold

        Jan - 18        1004            S           30     --- Prev Sold

        Jan - 18        1005            S           60     ---  New Sold

        Jan - 17        1006            W          90 

       

        Feb - 18        1001            S          100    --- New Sold

        Feb - 18        1002            S           70     --- Prev Sold

        Feb - 18        1003            S          150    --- Prev Sold

        Feb - 18        1004            S           30     --- Prev Sold

        Feb - 18        1005            S           60     --- Prev Sold

        Feb - 18        1006            S          90     ---  New Sold

        Feb - 18        1007            W         190 

        Feb - 18        1008            W          20

        Feb - 18        1009            B           30

       

        Mar - 18        1001            S          100    --- Prev Sold

        Mar - 18        1002            S           70     --- Prev Sold

        Mar - 18        1003            S          150    --- Prev Sold

        Mar - 18        1004            S           30     --- Prev Sold

        Mar - 18        1005            S           60     --- Prev Sold

        Mar - 18        1006            S          90     ---  Prev Sold

        Mar - 18        1007            W         190 

        Mar - 18        1008            S          20     --- New Sold

        Mar - 18        1009            B           30  

        Mar - 18        1010            W           30    

       

       

        Above is the data with the opportunities from Month Nov - 17 . Now i want to track the changes what ever happened in the stages like

        > how much worth of W/E/B opportunities from Nov-17 have converted to S in Month Dec-17

        > how much worth of W/E/B opportunities from Nov-17 have converted to S in Month Jan-18(Ideally this includes the opportunities with W/E/B in Nov and W/E/B in Dec has converted to S)

       

        At the end i want to track the opportunities converted to S from W/E/B stage.

       

      Can anyone please gimme an idea.

        • 1. Re: Track the movements
          Peter Fakan

          Hi Lalitha,

           

          Appreciate if you could upload some sample data, but I'm thinking something along the lines of this pseudocode;

           

          LOOKUP (SUM([Value]), (IF([Stage]) = "W" OR "E" OR "B" THEN SUM([Value])-1) ELSE 0))

           

          HTH

           

          Peter

          1 of 1 people found this helpful
          • 2. Re: Track the movements
            lalitha p

            I have attached sample excel with Dumpdate, Opportunity ID , Account, Booking, WEB , Type. In the same excel, with color representation i have made some samples which i want.

            Say,

            I want to display the bookings or the opportunities converted from W to S same from E to S and same from B to S.

            My main concentration is on opportunities with S.

            But ideally if i consider month Oct i already have a new opportunity(00628) with S and old opportunity(00626) converted from W to S.

            Here WEB i will make as a filter, So that if user selects W then 00626 opportunity booking value should display if filter is all then it should display both opportunities(ideally total sol@d, here selection of S is also fine.

            1 of 1 people found this helpful
            • 3. Re: Track the movements
              lalitha p

              I have got an idea but not sure how i can do it.
              I feel it is good to capture the previous WEB status into a field and then current WEB status into other field and then can compare those two fields.

              Suppose i select the filter date Sep and Oct 17 , I can able to capture the previous WEB value based on "Previou_value(attr(web) ) "  but i couldnt capture it for the latest WEB value as i have fllter the sheet for both sep and Oct. i am trying to get it form Lookup but not working as expected.

               

               

              Can anyone please help me!