2 Replies Latest reply on Oct 20, 2016 12:00 PM by Chris Forsha

    I bet you can't solve this one! Crazy table calculation and Bridge Chart! Salesforce Opportunity_HD table.

    Chris Forsha

      I'm trying to create a bridge chart that shows what happened to our September Pipeline over the month of September. The data is straight out of Salesforce Opp HD table joined with Opportunities. My Parameters are Changes From, Changes To, and Opportunities closing. At first, on advice from someone not too familiar with the problem, I created a calculated field basically bucketing my values, but this doesn't work for a couple reasons.

       

      1) Some of these buckets need to sum different measures.Won, Lost, Beginning Pipe, Ending Pipe and Pulled In are all using a column called Amount Historical.  Value Increase and Decrease are based on a calculated field called Amount Changed and Pushed Out is using Amount Prior. You say well just make another calculated field based on whether the line item is in which category but....

      2) Rows may fall into more than one bucket, which Tableau doesn't like either.

       

      I've created separate sheets for for each category which list the opportunities included and sums the appropriate value. But of course I can't reference those in a Gantt chart on a separate sheet. I don't know why Tableau doesn't allow for this. And there is no SUMIF function. Below is a mock up of what I'm trying to get to. I will get you a Starbucks if you can answer this for me. 

         

      Pipeflow Mock.JPG

       

      IF(DATE([Changes From])>= DATE([Valid From]))

          AND (DATE([Changes From])< DATE([Valid To]))

          AND (MONTH([Close Date (Historical)])= MONTH([Opportunities Closing]))

          AND (YEAR([Close Date (Historical)])= YEAR([Opportunities Closing]))

          AND (([Stage (Historical)]= "Demo")

              OR ([Stage (Historical)]= "Proposal")

              OR ([Stage (Historical)]= "Discovery"))

       

       

      THEN "Begining Pipeline"

       

       

      ELSEIF ([Changes From]< [CreatedDate (Opportunity)])

          AND (DATE([CreatedDate (Opportunity)]) <= DATE([Changes To]))

          AND (MONTH([Close Date (Historical)])= MONTH([Opportunities Closing]))

          AND (YEAR([Close Date (Historical)])= YEAR([Opportunities Closing]))

          AND (ISNULL([Close Date (Prior)]))

       

       

      THEN "Opened"

       

       

      ELSEIF (DATE([Valid From]) > DATE([Changes From]))

          AND (DATE([Changes To]) >= DATE([Valid From]))

          AND ((MONTH([Close Date (Historical)])= MONTH([Opportunities Closing]))

          AND (YEAR([Close Date (Historical)])= YEAR([Opportunities Closing])))

          AND ((MONTH([Close Date (Prior)]) <> MONTH([Opportunities Closing]))

          OR (YEAR([Close Date (Prior)]) <> YEAR([Opportunities Closing])))

         

      THEN "Pulled In"

       

      ELSEIF (DATE([Valid From])> DATE([Changes From])

           AND DATE([Changes To]) >= DATE([Valid From])

           AND [Revenue Change] > 0

           AND MONTH([Close Date (Historical)]) = MONTH([Opportunities Closing])

           AND YEAR([Close Date (Historical)]) = YEAR([Opportunities Closing])

           AND MONTH([Close Date (Prior)]) = MONTH([Opportunities Closing])

           AND YEAR([Close Date (Prior)]) = YEAR([Opportunities Closing]))

       

       

      THEN "Value Increase"

       

       

      ELSEIF (DATE([Valid From]) > DATE([Changes From]))

          AND (DATE([Changes To]) >= DATE([Valid From]))

          AND (MONTH([Close Date (Prior)]) = MONTH([Opportunities Closing])

          AND YEAR([Close Date (Prior)]) = YEAR([Opportunities Closing]))

          AND (MONTH([Close Date (Historical)]) <> MONTH([Opportunities Closing])

          OR YEAR([Close Date (Historical)]) <> YEAR([Opportunities Closing]))

       

       

      THEN "Pushed Out"

       

       

      etc.