6 Replies Latest reply on Jun 16, 2016 12:10 PM by P N

    Summing Revenue for a Month Based on a Point in Time Date

    Robert Murphy

      I am trying to sum transaction revenue based on a minimum start date for each respective Company among their divisions.  Below is the data set and desired outcome.

       

        

      Dates Table
      SupplierBuyerDivisionStart Date
      Supplier A        Company 1      Division 1         1/15/2016
      Supplier A       Company 1      Division 2         1/25/2016
      Supplier B        Company 1      Division 3         3/15/2016
      Supplier A        Company 2      Division 1         2/7/2016
      Supplier A        Company 2      Division 2         3/15/2016
      Supplier B        Company 2      Division 2         3/2/2016

       

           

      Transaction Data Table
      Tranactions Date for Transaction Dollars
      SupplierBuyerDivision1/15/20161/20/20161/25/20161/31/20162/7/20162/20/20162/28/20163/2/20163/15/20163/31/2016
      Supplier A        Company 1      Division 1         $100 $125 $ - $78 $ - $ - $ - $ - $ - $ -
      Supplier A       Company 1      Division 2         $ - $ - $200 $55 $ - $ - $ - $ - $ - $ -
      Supplier B        Company 1      Division 3         $ - $ - $ - $ - $ - $ - $ - $ - $150 $195
      Supplier A        Company 2      Division 1         $ - $ - $ - $ - $175 $112 $37 $ - $121 $127
      Supplier A        Company 2      Division 2         $ - $ - $ - $ - $ - $ - $ - $ - $212 $125
      Supplier B        Company 2      Division 2         $ - $ - $ - $ - $ - $ - $ - $323 $ - $545

       

      We have identified the right start date with the formula:

      {Fixed [Supplier], [Company]: Min([Start Date]) }

       

      But are stuck on how to sum the revenue

       

      We only care about when the Supplier's revenue in the month of the start date with the Company and not the Division.  However, the start date data is tied to the Divisions in our data source and then we have another table that ties Divisions to Companies and then another table with the transaction date.  The desired out put would be as follows:

          

      Desired OUTCOME
      First Month's Revenue
      SupplierBuyerStart DateJanuaryFebruaryMarch
      Supplier A       Company 1      1/15/2016$558 $ - $ -
      Supplier B        Company 1      3/15/2016$ - $ - $345
      Supplier A       Company 2      2/7/2016$ - $324 $ -
      Supplier B        Company 2      3/2/2016$ - $ - $868

       

      We have tried the following but it does not work:

      {Fixed [Supplier], [Buyer]: IF DATEDIFF( 'month' , [Start Date],[Transaction Date])) = 0

      THEN [Revenue]

      END }

       

      Attache is excel that visualizes the data sets.

        • 1. Re: Summing Revenue for a Month Based on a Point in Time Date
          Ivan Young

          Hi Robert,

          I think your main hurdle is the way you store your transaction data.  You can get pretty close to your desired view by pivoting the transaction data into a more normal format, the dates table is not needed.

          You would want to pivot all the dates and then filter out all the null amounts.   You could end up with a view like below, you won't be able to get Supplier, Buyer in the order you want as Tableau groups data.  But the best advice I can give you is store your transaction data in a normal way.

           

          Regards,

          Ivan

           

          • 2. Re: Summing Revenue for a Month Based on a Point in Time Date
            Robert Murphy

            Thanks Ivan.  We have the transaction data stored in a normal way for Tableau.  I just displayed in excel for easier presentation purposes.  What is your "calculation1" in your screen shot?

            • 3. Re: Summing Revenue for a Month Based on a Point in Time Date
              Ivan Young

              Hi Robert,

              That's good you have your trans data stored normally, Tableau works much better with normal data sources and you won't need to pivot the data.

               

              I'm assuming your transaction data is stored something like the example below.  Your LOD looks good but for this data it would be {Fixed [Supplier], [Company]: Min([TransDate]) }, I named dimension Start Date.

               

              Now we create the measure ZN(SUM(if MONTH([TransDate])=MONTH([Start Date]) then [Amount]  end))

               

              You then need to lay out the data as in the screenshot above and you should be good.  Please let me know if you have any questions.

               

              Regards,
              Ivan

               

              BuyerDivisionSupplierTransDateAmount
              Company 1Division 1Supplier A1/15/2016100
              Company 1Division 1Supplier A1/20/2016125
              Company 1Division 2Supplier A1/25/2016200
              Company 1Division 1Supplier A1/31/201678
              Company 1Division 2Supplier A1/31/201655
              Company 2Division 1Supplier A2/20/2016112
              Company 2Division 1Supplier A2/28/201637
              Company 2Division 1Supplier A2/7/2016175
              Company 1Division 3Supplier B3/15/2016150
              Company 2Division 1Supplier A3/15/2016121
              Company 2Division 2Supplier A3/15/2016212
              Company 2Division 2Supplier B3/2/2016323
              Company 1Division 3Supplier B3/31/2016195
              Company 2Division 1Supplier A3/31/2016127
              Company 2Division 2Supplier A3/31/2016125
              Company 2Division 2Supplier B3/31/2016545
              • 4. Re: Summing Revenue for a Month Based on a Point in Time Date
                P N

                Hi Robert/Ivan:

                 

                There is one more solution along the lines that Ivan explained. We can put a new Calculated Field: month([Transaction date])-month([Calc. Start Date]. I have put that as Calculation2. Use that in the filter shelf as an attribute and set the value to '0'. Below is the screen shot:

                Thanks

                1 of 1 people found this helpful
                • 5. Re: Summing Revenue for a Month Based on a Point in Time Date
                  Ivan Young

                  Hi PJ,

                  I like your solution of using a filter rather building the logic into a measure, definitely more elegant.  Since I'm a fan of Boolean I've modified it to MONTH([Valid From])=MONTH([Start Date]) and select True.  Same result of course.

                   

                  Regards,

                  Ivan