7 Replies Latest reply on Jan 3, 2014 2:36 AM by Jan Siroky

# Time series - scatter plot of daily sums and averages

I have time series data with energy consumption and outside temperature (sample each 5 minutes). I need to investigate relation between daily energy consumption (sum) and daily average outdoor temperature.

The only way I found so far is the following. Draw lines chart with two lines (daily sum of en. consumption and daily average of outside temp.). Then copy the data and past them as a new data source. Then I can draw the scatter plot I need and and try to find a polynomial model.

Is there easier way how to do this?

Thank you,

Jan

• ###### 1. Re: Time series - scatter plot of daily sums and averages

Hi Jan,

you can probably* skip the line chart stage by doing the scatter plot and then place DAY(your_datetime_field) on the Details shelf. If that doesn't work, please post the workbook and I will take a look.

Peter

*probably, depending on how your original data is arrnged

• ###### 2. Re: Time series - scatter plot of daily sums and averages

Hi Peter,

you solution works, thank you!

Jan

• ###### 3. Re: Time series - scatter plot of daily sums and averages

Hi Peter,

I see that data arrangement is very important.

Lest investigate a simple example. I have two energy meters with the following data:

energy A

10:00, 10 kW

10:10, 10 kW

10:20, 10 kW

10:30, 10 kW

10:40, 10 kW

10:50, 10 kW

11:00, 10 kW

energy B

10:00, 10 kW

10:30, 10 kW

11:00, 10 kW

When I compute hourly sums I will get 600 for A and for 20 B. However, both energy meters shall have the same hourly sum (10 kWh).

Is there any way how to compute sum based on integral, not only sum of elements?

Jan

• ###### 4. Re: Time series - scatter plot of daily sums and averages

Hi Jan,

I'm happy to hear the solution worked.

In answer to your follow up I think you have two choices. One option is to just keep the data at the hour mark, e.g. 10:00, 11:00 etc....

The other option, which I think will suit you well, is to change the aggregation from SUM to AVG (mean average). You do this by right clicking on the 'pill' and changing to average, as below. As you can see you can also select max or min as aggregations too.

• ###### 5. Re: Time series - scatter plot of daily sums and averages

Hi Peter,

Average solves this problem. However, in practice I cannot guarantee that data will be equidistantly sampled. What if I have the following data

10:00, 10 kW

10:09, 10 kW

10:11, 0 kW

11:00, 0 kW

Then the average consumption is in reality 10/6 however I will get 6.6667. In this case I need integration with respect to time domain. Can this be handled in Tableau?

Jan

• ###### 6. Re: Time series - scatter plot of daily sums and averages

I suggest you open up a new thread with this question, that way you are more likely to catch someone with the correct expertise.

It should be possible using table calculations.

• ###### 7. Re: Time series - scatter plot of daily sums and averages

Hi Peter,

thank you.