4 Replies Latest reply on Apr 7, 2015 11:49 PM by kettan

    Calculate monthly payment and display over time

    Brooke Bondur

      Hello Tableau Experts:


      I'm working with Salesforce data that we have put into a custom query on our SQL server. I've been asked to figure out a way to create a crosstab view in a worksheet that will display the monthly amount of the total Opportunity Value (Amount) when given the Term, Start Date and End Date.  I was able to figure out how to create a calculation that will display the date for each period between the Start Date and End Date (thanks to this post Generating more than one date with DATEADD function). I created a calculated measure that determines the monthly amount using the Opportunity Value and Term.  My challenge is how to display the monthly amount in each period that is displayed.


      The attached workbook has 4 opportunities as an example.  All but one of the opportunities has only one record that provides the total Opportunity Value.  The other record had the Opportunity Value broken out in SFDC directly so it is displaying in the view correctly.  This is an exception. Most of my data has just one line item (we call it Product) that has all of the inputs I'm working with.


      Any help or input would be appreciated.


      Thank you in advance!


        • 1. Re: Calculate monthly payment and display over time

          I would go for dynamic data reshaping with method #1 in  The Cross Join Collection  and thus have real data in crosstab cells.


          If you are allowed to create tables in SQL Server, then the simplest is to first create a table with all months.


          If you are not allowed to make date period tables in SQL Server, then you could cross join data with table-less lookup tables representing each month between first and last. It will probably be 1) one table for years with all years between min and max year cross joining 2) one table for months (1-12) cross joining 3) a sub-query for min date and max date so each resulting row gets a start of month and end of month date.


          Date period split questions are almost asked on a daily basis as documented in  FAQ: Open & Close Dates.  I think this is because there is no good way to solve it in Tableau.  Therefore I requested  Date period dimension type with built-in row split  a few days ago.  Please consider up-voting mentioned idea if you like to have Date Period Splits as a built-in feature in a similar way as the new splits coming in Tableau 9:  L.ive S.tream of 2014 - See Vertical Split from 1:10:59 to 1:12:04 | Tableau Conference 2014

          1 of 1 people found this helpful
          • 2. Re: Calculate monthly payment and display over time
            Poornima K



                 I also have same problem. Our Data also comes from SFDC, but we are not able to use any custom sql as here no Sql Server is involved. Kindly let me know, do u have any idea about how can we achieve this in Tableau Desktop 8.3


            Problem statement:


            Table T has rows with start date and end date columns

            Customer     Start date          End Date

            A                    01-Jan-15          31-Jan-16    

            B                    01-Feb-15          28-Feb-15

            B                    01-Mar-15          31-Mar-15


            Now the we require to list the month names with year (i.e MMM-YY) format between the two dates i.e.Min of start date and Max of End Date.


            In Tableau, I could create only the diff in terms of days, months, years as an integer between the two dates. But we would require is to have the set of Months lists so that we can put the same calculated field in Column shelf to create x-axis with the Months-Year set.


            So in x-axis , the elements would be Jan-15, Feb-15, Mar-15,.....,Jan-16.




            • 3. Re: Calculate monthly payment and display over time
              Tableau kumar

              We use the Datename function to extrat the parts of the DATE Data Type, And which hold string data type,

              mid( datename('Month', [Date]), 1,3) + " - " + mid(  datename('year', [Date]), 3,4)

              • 4. Re: Calculate monthly payment and display over time

                I am already familiar with your previous questions:


                2015-03-30    how to get month names between two dates in tableau

                2015-03-26    Tableau - Monthwise Data Distribution


                I see you haven't replied to solution provided in  Re: how to get month names between two dates in tableau  and believe your chance of getting a solution is much higher if you put your effort there.


                Your latest attachment in  Re: Tableau - Monthwise Data Distribution  is a twb file, which doesn't have data. You need to save it as a twbx (packaged workbook), which has data. If data is sensitive, you need to mask it. Some techniques can be found from links in  FAQ: Sensitive Data.


                You might not know it, but those who already have participated in your previous questions are among the best to answer your question. I wonder who can if they can't!


                Friendly advice:


                1) Be careful not to hijack other users' threads. In this case it would have been more appropriate with links only so eventual answers are shared in your own thread(s).


                2) Provide needed information. I say so, because I think it was vital in your  second question  to mention that you have a direct connection to SalesForce data and thus can't use custom SQL and also appropriate with a link to your  first question.  Looking at your 5 questions, I guess all of them are related to the same task.