Hi everyone
I would like to create 2 date filters and have explored the forums which by and large address 'sales'. My set up is different and I am not sure how to create date filters using calculated fields to return the values I am looking for.
I have ~40 T/F calculated fields. For each calculated field, I would like to return:
Date filter 1 -Current reporting period = Last completed month rate (number of T/number of encounters
+++ Number of encounters is a calculated field which is simply equal to Sheet1 (Count) so not sure if I needed to do this step?
Date filter 2 - Average (Median) rolling previous 3 months rate (average of the 3 mths) - that is the 3 months prior to the reporting month i.e. not including the reporting period
I would like the output to look like this as an example:
Current reporting period Average previous 3 months
0.282% 0.206%
I have attached a workbook with 5 months data. I have one tab called 11.1 which is one of the 40 calculated fields. If I can get this displaying 2 columns as above, then I can duplicate this x 39 and build all 40 results. At the moment, the rate is displaying the result for all the months in the data source.
I really hope someone can help. Thanks in advance
Jillian
Hi Jillian
My solution is attached (worksheet "Solution"):
1 - Note that I substituted a parameter called "Today for testing" for TODAY().
2 - Add a couple of calculated fields for filtering dates:
Reporting Period = DATEDIFF("month",[Discharge Date],[Today for testing]) = 1
Previous 3 Months = DATEDIFF("month",[Discharge Date],[Today for testing]) >= 2 AND
DATEDIFF("month",[Discharge Date],[Today for testing]) <= 4
3 - Add calculated measures for both date filters:
Reporting Period Count = IF [Reporting Period] THEN 1 END
Previous 3 Months Count = IF [Previous 3 Months] THEN 1 END
4 - Add both measures to the worksheet and do the same as you did with COUNT(Sheet1)
I didn't understand what you meant by "Median" in your description, the median of 3 months seems irrelevant.
In addition, if you could pivot your data by the 40 columns and get them as a dimension, you wouldn't need to duplicate the calculations (or worksheet) and you could report on all of them in one worksheet.
Dan
