I have daily data that looks like this:
<colgroup><col span="2"><col span="3"><col span="3"></colgroup><tbody>
</tbody>
I have used a pivot table to roll it up to summarize it by day (average) and then grouped it to get to a weekly level. Resulting in this:
<colgroup><col><col span="3"></colgroup><tbody>
</tbody>
I am using Filters 1, 2, 3 and Code as filters to in the pivot table to calculate:
Since I have to do more of these calculations with different filters on, my question is whether there is a clever way to code/retrieve the results (VBA, I assume) instead of me doing the calculations manually for each case? My concern is that switching filters on and off manually and copy/pasting results is very error-prone and I would really like to avoid it.
Thank you!
Date | Code | Filter 1 | Filter 2 | Filter 3 | Var1 | Var2 | Var3 |
5-Jan-14 | 3050 | Yes | No | Yes | 15.9 | 15.9 | |
5-Jan-14 | 3050 | No | No | Yes | 15.9 | 15.9 | |
5-Jan-14 | 5470 | No | Yes | Yes | 24.75 | 21.1 | 21.1 |
6-Jan-14 | 3050 | Yes | No | Yes | 15.9 | 15.9 | |
6-Jan-14 | 3050 | No | No | Yes | 15.9 | 15.9 | |
6-Jan-14 | 5470 | No | Yes | Yes | 24.75 | 21.1 | 21.1 |
7-Jan-14 | 3050 | Yes | No | Yes | 15.9 | 15.9 | |
7-Jan-14 | 3050 | No | No | Yes | 24.75 | 21.1 | 21.1 |
7-Jan-14 | 5470 | No | Yes | Yes | 28.55 | 24.5 | 24.5 |
<colgroup><col span="2"><col span="3"><col span="3"></colgroup><tbody>
</tbody>
I have used a pivot table to roll it up to summarize it by day (average) and then grouped it to get to a weekly level. Resulting in this:
Row Labels | Average of Var1 | Average of Var2 | Average of Var3 |
1/5/2014 - 1/8/2014 | 25.7 | 18.58888889 | 18.58888889 |
<colgroup><col><col span="3"></colgroup><tbody>
</tbody>
I am using Filters 1, 2, 3 and Code as filters to in the pivot table to calculate:
- Average of Var1 for the Week, but only where Code is 3050
- Percentage difference in Var1 for Code 3050 and Code 5470, calculated as (WeekAvgVar1-3050 - WeekAvgVar1-5470) / WeekAvgVar1-3050&5470
- The same as above, but with Filter 1 applied for the nominator and Filter 2 applied for the denominator
Since I have to do more of these calculations with different filters on, my question is whether there is a clever way to code/retrieve the results (VBA, I assume) instead of me doing the calculations manually for each case? My concern is that switching filters on and off manually and copy/pasting results is very error-prone and I would really like to avoid it.
Thank you!