7 Replies Latest reply on Sep 3, 2018 7:43 AM by Joe Smith

# how to get the averge of a given number of values prior to the current one?

I have a list of average temperature (T_avg) for each of the 365 days.

I want to calculate the "cumulative average" of T_avg of the 30 days prior to the current day.

E.g, the "cumulative avg temperature" for 31-Mar is the average of T_avg from 1 to 30 Mar.

I tried a while with the index() and running_avg() functions, but I didn't get to anywhere.

Thanks.

• ###### 1. Re: how to get the averge of a given number of values prior to the current one?

Hi, Joe

Have you tried Window_avg([your avg aggregation],-31,-1)?

ZZ

1 of 1 people found this helpful
• ###### 2. Re: how to get the averge of a given number of values prior to the current one?

Thanks, Zhouyi Zhang for your suggestion.

May I ask one more question?

Assuming I have 100 rows of data, i.e. index from 1 to 100. you method works for the data from index 32.

For the data rows with index <=31, how to apply the same calculation but including those data rows from the end of table, assuming the data table is in a "loop" kind of format.

E.g.:

for data row #30, the "average of the 30 values before it" is calculated from data row 1 to 29 and row 100.

for data row #29, the "average of the 30 values before it" is calculated from data row 1 to 28 and row 99  to 100.

for data row #28, the "average of the 30 values before it" is calculated from data row 1 to 27 and row 98  to 100.

..... and so on.

• ###### 3. Re: how to get the averge of a given number of values prior to the current one?

Hi, Joe

Doable. I use super store data as example for your reference. I use 5 steps back instead of your 31 just make it visible of data for your calculation.

If you can't figure out with your data, please provide a sample workbook.

ZZ

• ###### 4. Re: how to get the averge of a given number of values prior to the current one?

Thank you, Zhouyi Zhang.

I'm unable to replicate your solution because I'm not sure how the variable "Total Row#" is calculated.

An example workbook is attached for your reference.

The "prevailing mean outdoor temperature in the last 30 days" (PMOT) of a given day should be the average of the 30*24 hourly temperature data prior to this day.

E.g.

Each of the 24 hours on 31-Jan shall has the same PMOT value which should be the average of the 30*24 hourly temperature values from 1-Jan 1:00 to 30-Jan 24:00

Each of the 24 hours on 1-Feb  shall has the same PMOT value which should be the average of the 30*24 hourly temperature values from 2-Jan 1:00 to 31-Jan 24:00

Each of the 24 hours on 2-Feb  shall has the same PMOT value which should be the average of the 30*24 hourly temperature values from 3-Jan 1:00 to 1-Feb 24:00

Each of the 24 hours on 3-Feb  shall has the same PMOT value which should be the average of the 30*24 hourly temperature values from 3-Jan 1:00 to 2-Feb 24:00

... and so on

The PMOT used in the demo workbook is actually not correcct in this sense.

• ###### 5. Re: how to get the averge of a given number of values prior to the current one?

Dear Zhouyi Zhang, appreciate if you can kindly take a look of the example Tableau workbook file I attached above, and I hope I'm not interrupting you too much.

Thanks a lot.

• ###### 6. Re: how to get the averge of a given number of values prior to the current one?

Hi, Joe.

Sorry about the late response, I wasn't well last week.

Not quite sure to your workbook. do you mean for a particular hour in a day, should get the average of last 30 days of the same hour?

ZZ

• ###### 7. Re: how to get the averge of a given number of values prior to the current one?

Thank you for your reply, Zhouyi Zhang. Wish you a speedy recovery.

What I want to do is that, for a particular day, to get the average of all the hourly values of the last 30 days, not the average of the last 30 days of the same hour.

It would be equivalent to adding a new column "avg temperature of the last 30 days" to the table shown below.

Please let me know if my explanation is still not clear to you.

Thank you, again.