2 Replies Latest reply on Nov 26, 2012 11:46 AM by SILVIO MARTINEZ

    How Can I Bring Baseline Data forward and Compare it to Active or Current Results

    SILVIO MARTINEZ

      I am beating myself up trying to figure this out!  Need some help.

       

      I have 2 sets of data (Go-Live & Baseline) which I compare on a quarterly basis. Below is the desired view.  Now keep in mind that the Baseline data column started in 2010 Q1 thru 2010 Q4 (1yr data).  The Baseline number of 8.1  is an average of baseline data and is just carried forward and used to compare against "Go Live" or "Post" data which starts in 2011 Q1.

       

      Desired result.jpg

       

      Items to remember

      • "Post" means the same as "Go Live"
      • "Post" results werer calculated Hospital Days / Discharges
      • Variance - Days (Baseline vs Post) 
      • Percentage Change (Baseline vs Post) = ("Post" - "Baseline") / "Baseline"

       

       

      Below is what I have so far but I can't seem to calculate the things correctly or get the "Baseline" rows (2010 Q1 - Q4) to automatically hide as all I need to see is "Baseline" data when "Post" data is available.  I think I need a calculation for "Post" and a separate calculation for "Baseline"?

      Current Value.jpg

       

      Attached is the workbook with sample data (Not Real).  Any assistance would be greatly appreciated.

        • 1. Re: How Can I Bring Baseline Data forward and Compare it to Active or Current Results
          Kenneth McBride

          The issue comes down to the “Baseline” value. You have it in your data for 2010 but not for any dates after 2010. When trying to calculate the Variance – Days (Baseline vs Post) and the Percentage Change (Baseline vs Post) for any dates past 2010 you’re going to have an issue because there is data for post but not for baseline.

           

          Using the workbook you supplied I created some additional calculated fields:

          1. Baseline Hospital days- to calculate the # of hospital days for baseline. I created this so I can reference it later.
          2. Baseline- this is a table calculation to do a running average of the baseline hospital days calculated field. (to get that 8.1 value to extend down the table into 2011 & 2012 even though there aren't any values for baseline in 2011 & 2012)
          3. Post- a calculated field which takes hospital days/discharges
          4. Variance Days (Baseline vs Post)- a table calculation which calculates the difference between Post and baseline down your table.
          5. Percentage Change (Baseline vs Post)- a table calculation which calculates the percentage change between baseline and post down your table.

           

          After setting up the view and placing these fields the final step was to hide the 2010 row. Workbook attached. I also emailed you another possible solution.

          1 of 1 people found this helpful
          • 2. Re: How Can I Bring Baseline Data forward and Compare it to Active or Current Results
            SILVIO MARTINEZ

            This gets me moving in the right direction.  However, this doesn't provide me with a Grand Total which calculates correctly.  Below are the correct Quarterly & Grand Total numbers.  In addition, how can I create the below graph.  I can only create for the "Post" and not the "Baseline". Any ideas for both?

             

            Desired View_121126.jpg

             

            Below is the report with incorrect Grand Totals:

             

            Current View_121126.jpg

             

            Also, any ideas or solutions on how to automatically hide the rows with "Baseline" data rather than hidden them for each produced report?

             

             

            Attached is the file I have been playing with.