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:
- Baseline Hospital days- to calculate the # of hospital days for baseline. I created this so I can reference it later.
- 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)
- Post- a calculated field which takes hospital days/discharges
- Variance Days (Baseline vs Post)- a table calculation which calculates the difference between Post and baseline down your table.
- 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.
Need Help.twbx.zip 704.8 KB
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?
Below is the report with incorrect Grand Totals:
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.
Need Help_121126.twbx.zip 814.3 KB