9 Replies Latest reply on Apr 27, 2017 3:57 PM by Shinichiro Murakami

    Rolling Pipeline Calculation

    William Graf

      Hi there!

       

      I'm working with patient appointment data for a medical practice and I'm trying to get an idea for their pipeline (i.e., how many appointments have been scheduled in the future and the dollar amount associated with those appointments).

       

      I've been able to get a point in time calculation to work with the help of a parameter. It's dynamic in the sense that I can say "what would the pipeline have looked like as of [Parameter Date]?"

       

      What I'd like to do is get a bar chart showing this calculation at various points in time. Each bar would represent what the pipeline looked like at the end of a calendar month (e.g., pipeline as of end of December 2015, January 2016, February 2016, etc.)

       

      I've attached a packaged workbook containing what I currently have. If anyone could help me set this up so I could see the evolution of the pipeline over time without having to manually adjust the parameter date over and over I'd be grateful!

       

      Thanks,

      Bill

       

      p.s., I'm using Tableau 10.2.1

        • 1. Re: Rolling Pipeline Calculation
          Karthik Venkatachalam

          Hi William,

          Not sure if this is what you were looking for. This should take display months side by side, but for all the period that you have data for. I moved the parameter back to 2000. You can essentially remove it as well.

          I used discrete fields, you can see from the pills as shown. Ignore the AppDate(copy).

          • 2. Re: Rolling Pipeline Calculation
            William Graf

            Thanks for the post, but that's not quite what I'm looking for. What I'm trying to get is a bar chart that looks like the following:

             

             

            The value of each bar is the output if you set the "Pipeline As Of" parameter to the dates listed. Hopefully this clears things up.

            • 3. Re: Rolling Pipeline Calculation
              Shinichiro Murakami

              Hi William

               

              This requires completely different approach.

               

              Create date master to cover required range of dates..

               

              Connect Date master data source and blend two data with date master as primary and linked with "date" and "Apptdate"

               

               

              [Calculation1]

              if isnull(window_sum(SUM([DDB_APPT (Dentrix)].[Amount]),first(),0)) then 0

              else window_sum(SUM([DDB_APPT (Dentrix)].[Amount]),0,last()) end

               

               

               

               

              Thanks,

              Shin

              • 4. Re: Rolling Pipeline Calculation
                William Graf

                Shin,

                 

                Thanks for the response, but that's not quite what I'm looking for. Per my prior post, I'd like each bar to represent the pipeline value in my original workbook if you set the "Pipeline As Of" parameter to that date. For example, 2/1/2005 should be $24.88M (as opposed to $84.9M in your sheet), 3/1/2005 should be $23.63M (as opposed to $75.9M in your spreadsheet), etc.

                 

                I believe the calculation that your workbook shows is the cumulative "Amount" between the "Date" and the end of the data set. I think the missing piece of the puzzle here is to exclude appointments that have a "Createdate" of prior to the calculation period from the calculation. I'm just not sure how to accomplish this.

                 

                Thanks,

                Bill

                • 5. Re: Rolling Pipeline Calculation
                  Shinichiro Murakami

                  William

                   

                  Exact same approach, just I missed the dates.

                   

                   

                  // Only show true = every 1st date of month

                  [Filter]

                  lookup(min([Date]),0)=lookup(min(datetrunc('month',[Date])),0)

                   

                   

                   

                   

                  Thanks,

                  Shin

                  • 6. Re: Rolling Pipeline Calculation
                    William Graf

                    Thanks for the quick reply, Shin. This looks closer. Two follow-ups:

                     

                    • I forgot to include the "Exclude Appointments" filter on the "Total Pipeline Value" tab... including this, the proper value for 2/1/2005 would be $2.20M. These are the values that I actually am trying to get at in the rolling chart. I've attached an updated workbook that includes this filter.

                     

                     

                    • Secondly... When I take your workbook and replace the embedded data with the live data source, the graph disappears:

                     

                     

                     

                     

                    I guess I'm just not familiar enough with the WINDOW_SUM function to understand why the inclusion of more data (i.e., the whole data source) would break the calculation.

                     

                    Apologies for all the back and forth here. I really appreciate the help.

                     

                    Thanks,

                    Bill

                    • 7. Re: Rolling Pipeline Calculation
                      Shinichiro Murakami

                      I cannot open you attachment, then replicate in my book.

                       

                       

                      Data replacement is one of most troublesome task in Tableau.

                      Ton of issues there.

                       

                      FAQ: Replace Data Source

                       

                       

                      To make filter work, you need to re-shape the data.

                       

                      Copy required dates from excel or something.

                       

                      And under edit data source screen, paste as connections.

                       

                       

                       

                      Same formula and filter on DDP-APPT data.

                       

                       

                      Thanks,

                      Shin

                      • 8. Re: Rolling Pipeline Calculation
                        William Graf

                        I still don't think this get's to the solution, unfortunately. I've put some more thought into it and have attempted to break the problem down even further. I believe all I actually need to do is calculate nested WINDOW_SUMs, but I can't seem to figure out how. I effectively just need to sum a rectangle of values (see below).

                         

                        I've performed the calculations I'm looking for in the attached excel workbook. See cells Z429 and AT429 for the expected output for January 31st and February 28th, respectively.

                         

                         

                        I'm hoping it might be a bit easier at this level of abstraction relative to what we were working with before. Again, thanks for your continued help, Shin.

                         

                        Best,

                        Bill

                        • 9. Re: Rolling Pipeline Calculation
                          Shinichiro Murakami

                          William,

                           

                          I'm sorry, it's becoming too much to me.

                          Little bit exhausted.

                          I don't have power to to variate all the raw data.

                           

                          Shin