Build an accumulation table to help your app understand how to accumulate the hours. The table will look like this:
Accum2hr:
| HourOfTheDay | Accum2hour |
| 1 | 2 |
| 2 | 2 |
| 2 | 3 |
| 3 | 3 |
| 3 | 4 |
| 4 | 4 |
| 4 | 5 |
| 5 | 5 |
| 5 | 6 |
| 6 | 6 |
| 6 | 7 |
| 7 | 7 |
| 7 | 8 |
| 8 | 8 |
| Etc…. | Etc …. |
Accum4hr:
| HourOfTheDay | Accum4hour |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 2 | 5 |
| 3 | 5 |
| 4 | 5 |
| 5 | 5 |
| 3 | 6 |
| 4 | 6 |
| 5 | 6 |
| 6 | 6 |
| Etc…. | Etc …. |
Then join your data on the Hour column in your base data to the HourOfTheDay value in the table(s). What this does is tell your application that in the 2nd hour, the Accum2 values to accumulate are 1 & 2 and so forth. So you can now use the Accum<n> value(s) as a virtual dimension(s) to calc those values. Ie sum(<{Accum2Hour={[HourInYourDataField]}> fieldtototal} – but be careful of potential data duplication if you use the accum<n>hour field in the dimensions of the chart.
You may also want to review the posts on “accumlations in pivot tables”. Below is a code snippet I use to build a daily accumulation table
// Build the accumulation mapping table for the hours of a day
for vOuterLoop = 1 to 24
for vInnerLoop = 1 to vOuterLoop
HourAccumulation:
LOAD
$(vOuterLoop) as [AccumulatedHr],
$(vInnerLoop) as [InvHr]
AUTOGENERATE(1);
next
next
Hope this helps…
