5 minute to 15 minute interval data conversion

Hi, I have pricing data that I get in 5 minutes intervals. I need to convert this pricing data to a 15 minute average. It looks like below.

Basically I'm trying to create column C below that is an average price per 15 minutes. How can I do this in Alteryx?

Date Time

Accepted answers

apathetichell

Is this the output format that you wanted?

I took the earliest time and appended it. Then I created a key based upon floor division of 15 minute intervals from the starting time (datetimediff with minutes) and compared it to the previous row to create groups. Then I used those groups to create average values...

This would be scalable if you had greater than 3 (or less than 3) values per 15 minute interval.

moving 15 minute average.yxmd

All comments

mceleavey

Hi @marlylove ,

Can you post your data so we can build it for you?

marlylove

Here is the data file.

LMP 5 minute data.xlsx

marlylove

Just added my file. Thanks!

Kenda

Hello @marlylove

Try using a Multi-Row Formula tool to create the new field with the following expression (where Field1 is your date/time field and Field2 is your value field):

iif(isempty([Row-2:Field1]),null(), iif(right([Field1],2)="00" || right([Field1],2)="15" || right([Field1],2)="30" || right([Field1],2)="45", average([Field2],[Row-1:Field2],[Row-2:Field2]),null()))

This checks to ensure the time is at each 15 minute mark then calculates the average that you're looking for. Hope this helps!

apathetichell

Is this the output format that you wanted?

This would be scalable if you had greater than 3 (or less than 3) values per 15 minute interval.

moving 15 minute average.yxmd

marlylove

Hi Kendra,

I'm getting a parse error, when I try below. I'm not familiar with writing multi row formulas, so I'm having trouble finding the problem.

iif(isempty([Row-2:[Date/Time]),null(), iif(right([Date/Time],2)="00" || right([Date/Time],2)="15" || right([Date/Time],2)="30" || right([Date/Time],2)="45", average([HB_NORTH (RTLMP) Average],[Row-1:[HB_NORTH (RTLMP) Average],[Row-2:[HB_NORTH (RTLMP) Average]),null()))

marlylove

So after I have the 15 minute average prices, how do I filter it to just so those prices?

Nevermind, I figured this out.

Thanks for the help!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703