7 Replies Latest reply on May 18, 2015 6:17 AM by Jonathan Drummey

    Is it possible to create psedo months and divide value between them ?

    Samith Mohan

      I am trying to develop a chart to represent Demand vs capacity and data is fetched from SFDC

      I need to distribute the demand data equally across the months between respective start and end date.

       

      E.g.: There is a demand of 100 hours from Jan-15 to May-15. Then we need to show this as a chart with month from Jan to May in X axis and with value 20 each

       

      Let me know if there is any way of doing it in Tableau 8.1 or 9.0 ?

       

      NOTE : I know this is possible if I can use some staging table (Eg: using SQL server) in between. But in this case we have to use the data directly from SFDC

        • 1. Re: Is it possible to create psedo months and divide value between them ?
          Cyril BELMEHDI

          You need to be more specific and send and example with a workbook if possible.

           

          What you're asking for looks like a simple window average but an example would help.

           

          WINDOW_AVG(sum([MYMEASURE])) will display 20 if you have the month and your measure in your chart

           

          Thanks

          1 of 1 people found this helpful
          • 2. Re: Is it possible to create psedo months and divide value between them ?
            Samith Mohan

            Hi Cyril,
            Thanks for your response. But unfortunately the solution you mentioned will not exactly suit my requirements. My apologies for not explaining the requirements elaborate enough

             

            Let me try to put more details. (Also find attached the excel "Capacity_Demand_graph_Details.xls" where I added more details in seperate tabs)

             

            1. The basic requirement is to create a demand vs capacity graph where capacity will be a bar chart and demand will be a line chart overlapping it (Please refer the tab "Sample Report"). The chart will give an idea about the demand for each month and if company has got the capacity to achieve the demand

             

            2. Capacity data is a pretty straight forward one where we will have different resources and their respective available capacity for each month. Hence its easy to plot that and we have no issues with it (please refer the tab "Capacity" to see how the input data will look like)

             

            3. The problem is with the demand data representation. Here we have different resource requests (or call it demand) over a span of time. The input data will be a specific effort in hours between a start and end date (please refer the tab "Demand" for input data format). Here the requirement is to divide the demand hours among the months between start and end date equally

             

            4. To elaborate let me take an example. Lets take Resource Request 1 in this case. The total hours is 400 and start and end date is 1st Apr to 31-Aug. In "Sample Report" tab if you check the table below the graph you can see how we divided the hours between Apr and Aug in to 5 equal chunks of 80 hours each. Similar calculation must be done for all Resource requests and then it should be used in the graph

             

            With the WINDOW_AVERAGE we can get an average effort between different Resource Requests, but since in this case we need to first divide the total hours among each month between start and end date and then use it in the graph this solution will not help

             

            The real challenge for us is that our input data is coming from Salesforce and hence all the manipulations needs to be done direcly in Tableau. We cannot use any intermediate system to adjust the data (like using a procedure in SQL etc)

             

            Please let me know if you need more details

             

            Thanks in advance,

            Samith

            • 3. Re: Is it possible to create psedo months and divide value between them ?
              Cyril Belmehdi

              Thanks for the details, it's a bit more clear.

               

              Can you explain how you're supposed to get the date in yellow in your file ?

               

              Basically you don't want to have your kind of average you also want a controled cartesian product to create the periods in yellow.

               

              Getting the 80 is easy, you need you do a datediff calc in month between start and end date and then divide the total.

               

              But you have another problem here, you said that diff between  1st Apr to 31-Aug is 5 but tehcnically it's 4 with any date function. You need to be more specific on what you expect.

               

              I never used salesforce, so I don't know it's source edition capacity. Can you do sql ?

               

              Thanks

              • 4. Re: Is it possible to create psedo months and divide value between them ?
                Cyril Belmehdi

                Here is something to illustrate what I said. I did a number of day / 30 to have 5 instead of 4.  Capture.PNG

                • 5. Re: Is it possible to create psedo months and divide value between them ?
                  Jonathan Drummey

                  I've given a solution for this in this thread: Tableau - Monthwise Data Distribution

                   

                  The posted data is exactly what was posted in that thread as far as I can tell, as is the question. Please don't do that as it can end up wasting our effort. (And in fact, your description of the data and problem would have been useful in helping me arrive at the solution that I did faster).

                   

                  Jonathan

                  • 6. Re: Is it possible to create psedo months and divide value between them ?
                    Samith Mohan

                    Thanks Jonathan !

                     

                    We were able to fix the issue related to monthly data split by using a separate excel data source having different combinations of months/years. We used this excel as primary and blended the other two SFDC data sources and achieved the month wise split as expected

                     

                    The new issue we have is related to the filters where we are unable to filter using fields other than blended fields. I saw your response in another thread where you mentioned that Tableau currently allows filtering dimensions from primary data source only in cases where we use multiple data sources.

                     

                    We are looking at other alternatives there and will let you know if we face more challenges.

                     

                    However we are at least able to create a report with month wise split now. Appreciate your detailed informative feedbacks!

                     

                    ~Samith

                    • 7. Re: Is it possible to create psedo months and divide value between them ?
                      Jonathan Drummey

                      You're welcome!

                       

                      The blend solution to the filtering problem is to increase the granularity of the scaffold source so it includes the filtering dimensions as well, then you can add them as linking dimensions so the filters will effectively be applied to the second source (via the primary source).

                       

                      Jonathan