4 Replies Latest reply on Dec 12, 2017 9:54 PM by Zhouyi Zhang

# Secondary Table Calculation using different value details

Hi folks,

I came across an interesting challenge and was wondering if any of you may have dealt with something like this.

I have a table with values for each weekday (down) and hours of the day (across). Here's the task:

(1) first, create a moving average of the 10 previous weekdays (excluding weekends, applied a filter): accomplished by using table calculation, moving average, previous 10

(2) secondly - and here it gets interesting: average the first 3 of the previous 4 hours for a given hour, i.e. moving average created in (1) above for e.g. hour 10 of the day should now average the hours 6, 7 and 8 (and not 9) to compute the new averaged value.

Basically, it's applying 2 moving average calculations, first down for previous 10, then across for previous 4 using only first 3.

I'm thankful for any hints and ideas!

• ###### 1. Re: Secondary Table Calculation using different value details

Hi, Wolfgang

I hope I understand your requirement correctly. Please see my below result and attached workbook.

Hope this could help

ZZ

• ###### 2. Re: Secondary Table Calculation using different value details

Thanks Zhouyi!

• ###### 3. Re: Secondary Table Calculation using different value details

ZZ, I have another question related to the result of this calculation.

How may I plot this on a graph? When I visualize it, the Calculations appear to be using data from "across" rather than "down" for the first of the 2 calculations.

The attached screenshot shows the original value (Total kWh) and the 2 calculations. From what I see Calculation 1 (red) that is supposed to use the last previous 10 days for each specific hour of the day seems to be plotting a 10 hour average, i.e. computes "across" rather than using the results from the "compute down" in the table.

Any advice you can give on how I could simply use the results of the table calculation and plot them?