5 minute to 15 minute interval data conversion

[marlylove]

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?

[mceleavey]

Hi @marlylove ,

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

M.

[marlylove]

Here is the data file.

[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 · Accepted solution]

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.

[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()))

[apathetichell]

hi @marlylove give mine a try... It uses a different strategy...

also it's [Row-2:Date/Time] - no second "["

The problem with that approach is that two times ending in "30" would get the same group setting - so 1:30 and 3:30 would end up in the same group. It's not a precise measure - using the datetimediff function and calculating the floor division by 15 will give you the correct result and work for multiple instances of the same minutes across different hours and the same times across different days.

You could also create a column of minutes vs earliest time and go off of that via floor division as well.

[Kenda]

Hi @marlylove 

It looks like you have an extra bracket in your expression. 

Instead of: [Row-2:[Date/Time]

Try: [Row-2:Date/Time]

This goes for all of the pieces of the expression that reference a different row. Hope this helps!

[marlylove]

This calculated correctly.  Thank you!