1 2 3 Previous Next 34 Replies Latest reply on Jul 22, 2015 5:15 PM by Angela Saffin

    Tableau - Monthwise Data Distribution

    Poornima K

      We have an requirement to display the data as month basis - where data has to be distributed equally from start date to end date. Kindly provide some idea how can we achieve this in tableau. The start date and end date are different columns but x-axis should be on month basis for each year by default. According to the month if data lies between the start date and end date then graph should appear in each month between them. For example start date is 1/Jan/2015 and End Date is 31/Jan/2016 then over the period for every month data has to be distributed across the months between 1/Jan/2015 and 31/Jan/2016.

       

      Table sample:

       

      Customer| Start date | End date              | Data

       

      ABC        | 1/Jan/2015 | 31/Jan/2016         | 1000

       

      ABC        | 1/Feb/2015 | 28/Feb/2015       | 200

       

      XYZ         | 1/Jan/2015 | 31/Jan/2015         | 60

        • 1. Re: Tableau - Monthwise Data Distribution
          kettan

          I wonder if attached is something you can use?

           

          The first step was to split the period into months with method #1 in  The Cross Join Collection:

           

          SELECT *
          FROM [data$] d, [lookup$] l
          WHERE d.[Start date] <= l.[Month End]
          and d.[End date] >= l.[Month Start]
          

           

          If you use Excel or similar file as data source, you need to open it with the legacy connector:

          Open with Legacy Connection 2.png

           

          Thereafter following formulas were created and added to the view:

           

          Days Period

          DATEDIFF('day', [Start date], [End date]) + 1
          

           

          Days Month

          DATEDIFF('day', MAX([Month Start], [Start date]), MIN([Month End], [End date])) + 1
          

           

          Data Month

          [Data] * [Days Month] / [Days Period]
          

           

          thread 159190 Tableau - Monthwise Data Distribution.png

           

          I wish there was a built-in way to split date periods into multiple rows.

          If you agree, you might like to up-vote:

           

          Date period dimension type with built-in row split

          Support cross product / cross join / Cartesian join / Cartesian product in new connection interface

          Support custom WHERE clause in new connection interface

          .

          • 2. Re: Tableau - Monthwise Data Distribution
            Jonathan Drummey

            It's also possible to get to a spread out view using Tableau v9's pivot feature, Show Missing Values, and a table calculations, however additional calculations on that padded out data would have to build on the table calc and could become difficult. @kettan's cross join gives you the padded data as record-level fields and is easier to manipulate in Tableau, so that's what I'd recommend.

             

            Jonathan

            • 3. Re: Tableau - Monthwise Data Distribution
              Poornima K

              Thank you for the detailed description. Your explanation is well understood and satisfying our requirement, but it has directly the data for each month in the view.

               

              We would not be able to use the cross join and custom SQL concept in our source connection, as it is the data fetch from Sales force.

               

              Also in our Data view, we have only start date and end date with respected data column, the aim is to have Month-Year list(may be pre-defined set or calculated field) in column shelf and if any month-year lies between the start date and end date the corresponding distributed data column should display as in the screenshot.SFDC_1.png

              • 4. Re: Tableau - Monthwise Data Distribution
                kettan

                I am afraid I don't know any solution for you.    Jonathan Drummey  previous comment might be a way to go. If he doesn't knows a solution for this, I am afraid nobody knows.

                 

                This is a good example of the need of having a built-in solution for date periods such as  Date period dimension type with built-in row split  and also exhibits a weakness in cloud solutions.

                • 5. Re: Tableau - Monthwise Data Distribution
                  Jonathan Drummey

                  I've got some other ideas for a solution, however I have a question about your graph. You're showing a line that is going up and down, however for a given company you'd stated that the Data value would be evenly distributed across the entire period, so it would be a straight horizontal line for that company. The only way I can reconcile those two statements is to presume that you're looking to sum up all the company values for each month other than showing individual companies. Is that correct?

                   

                  The reason that I ask is that in situations like this where there's got to be some sort of transformation of the data to enable the display it's necessary to have a good idea of the final desired outcome. The reason why is that although there may be multiple routes to a solution, some may not be suitable due to various conditions. (As another example of this, you hadn't stated that you were using a cloud data source that rules out a cross product query).

                   

                  Jonathan

                  • 6. Re: Tableau - Monthwise Data Distribution
                    rohail khan

                    Can any one Past Exam Papers regarding this Exams i Have a Plan in this Month to Get this Certification so any one who take this Exam Please Share Experience Like( Difficulty Level of Paper,Practice need) etc and Please send me Past exam Papers

                    • 7. Re: Tableau - Monthwise Data Distribution
                      Poornima K

                      Hi Jonathan,

                       

                                 Sorry for not stating the source of the data in the problem statement.

                       

                            And with regards to graph representation, its a overall customers data - where multiple number of customers present with their own data value for different month according to the opportunities.

                       

                      Thanks

                      Poornima

                      • 8. Re: Tableau - Monthwise Data Distribution
                        Poornima K


                        Hi,

                         

                        Please find the attached workbook.

                         

                        We have three data sources from Sales force with Contact as common field as attached files.

                         

                        We need to create single view with both buy and sell details according to the moths.

                        We have taken Contact as the primary source so that all contacts name are available for filter and blending the other two sources with Contact name as key field.

                         

                        Is there any way we can list all months as calculated field and put that in column shelf and represent both sell and buy details with dual axis according to their month.

                         

                        But we are trying to do the graph with months as x-axis and the sell and buy count in row shelf with dual axis.

                         

                        But we are not able to map the second and third data source with date field. Sample pic is attached.req.png

                        • 9. Re: Tableau - Monthwise Data Distribution
                          Poornima K

                          Please find the updated packaged workbook.

                          • 10. Re: Tableau - Monthwise Data Distribution
                            Poornima K

                            Hi All,

                             

                                    Please suggest any solution, as this will solve our major problem in creating Tableau dashboards.

                             

                            Thanks

                            Poornima

                            • 11. Re: Tableau - Monthwise Data Distribution
                              Jonathan Drummey

                              Hi,

                               

                              I took a look at this and I think you're running into one of the common misapprehensions about data blending, which is that dimensions from secondary sources that are at a finer granularity will "merge into" or "increase" the granularity of the result set. They don't. The granularity of the primary data source is what determines what is available for the blend. For example, in your case, the primary Contacts source only has the customer names, whereas the other two sources have one or more dates for each customer. So when you bring the secondary date dimensions into the view they either return * (when there is more than one date) or a single value.

                               

                              In order to do this with a reasonable chance of it working you'll need to build a scaffold source outside of Tableau that has all months & all customers. Then you can use that scaffold source as the primary and then create calculations that use the Buy source & Sell source to appropriately display the data. I'm not sure what those calculations would look like, there are additional questions that would need to be answered:

                               

                              - Can there be overlapping buy & sell periods for a given customer (in other words, can a customer have multiple buys active at the same time?)

                              - Can there be buys with no sell yet? If so, how would those need to be displayed?

                              - Are there further aggregations you are looking to do, such as a roll-up view showing the sum or count across all customers?

                               

                              Jonathan

                              • 12. Re: Tableau - Monthwise Data Distribution
                                Poornima K


                                Hi Jonathan,

                                 

                                       Thank you..

                                 

                                      Yes there would be a scenario like, only buy data or only sale data is available for an customer for one period. There may be overlapping also.

                                 

                                     We were trying achieve the same with Months in parameters - so that we can at least show one Month data at a time. But this would not be efficient solution as parameters has to be updated with month-year over the years.

                                 

                                    As our data comes from Sales force, we are afraid that we could are able to build a scaffold source outside of Tableau or not.

                                 

                                    Thank you again..

                                 

                                Poornima

                                • 13. Re: Tableau - Monthwise Data Distribution
                                  Jonathan Drummey

                                  I don't think I have any easy answers for you. Here's how I frame the problem.

                                   

                                  - The ultimate goal is to do a display of accruals over time, where revenue is spread out over a period.

                                  - The data is coming from Salesforce.

                                  - The data is sparse - there may be buy dates with no sell dates or vice versa in a given period. How those need to be displayed hasn't been specified.

                                  - There are two separate data sources that need to be linked together in some way. One has buy dates, the other has sell dates, the only known common field is the Contact.

                                  - The data is sparse - there may be buy dates with no sell dates or vice versa in a given period. How those need to be displayed hasn't been specified.

                                  - There may be overlapping buy/sell timeframes for a given customer. How those would be identified hasn't been specified.

                                   

                                  In many ways, this is an ETL (Extract/Transform/Load) problem, and Tableau is not an ETL tool (though it does have some ETL features) so we have to give Tableau some help. To display this using Tableau only, you're going to need a source that has at least three columns for the date, contact, and potentially another column for identifying those overlaps (which might be something like a purchase ID). You could build this by downloading (I'm not sure how, I'm presuming Salesforce can do this) the list of contacts & any other info and then using a spreadsheet or a database tool that has an additional table with all the dates create a SQL query or view that does a cross product between them to generate the necessary scaffold source. Then you'd need to build some calculations in Tableau to link up the two sources to do the appropriate accrual calculations.

                                   

                                  Once you've done all this, the parameter solution might seem easier.

                                   

                                  An alternative would be to use a third party ETL tool like Alteryx or Informatica to grab the data and pre-process it to make a data set, if I were doing that I'd likely do as much of the pre-computation as possible for the accruals so the data set Tableau gets is ready for display and doesn't need more calculations.

                                   

                                  Jonathan

                                  • 14. Re: Tableau - Monthwise Data Distribution
                                    Poornima K

                                    Hi Jonathan ,

                                     

                                    :-(...This is very disappointing as the resultant dashboard we are not able to create. Parameter idea got rejected as we have to show the report across 12 months from the current month.

                                    Those 12 months from current month we take manually also. we tried to create 12 calculated field , but issue would be how we can place all in column shelf as single axis it can create.

                                    Also Buy source , we are supposed to take as primary , as with respect data at SFDC for any period/month selling data may not be there also.

                                     

                                    Please find the attached excel sheet, where in we have created the sample report, which is expected in tableau..

                                    Hope you may get some idea/solution. Please share some solution..

                                     

                                    Thanks

                                    Poornima

                                    1 2 3 Previous Next