Average Calculation

[rrahu07]

can someone please help me to calculate average of between line 1 and line 2, then line 2 and line 3, line 3 and line 4? Also, I want to make sure in moving average calculation I am not combining last time entry of the day and the first entry of next day.

Name	Time
A	9:01:00
A	9:02:18
A	9:05:56
A	9:06:43
A	9:07:25
A	9:08:48
A	9:12:01
A	9:21:12
A	9:34:29
A	9:40:37
B	9:01:00
B	9:02:18
B	9:05:56
B	9:06:43
B	9:07:25
B	9:08:48
B	9:12:01
B	9:21:12
B	9:34:29
B	9:40:37

I tried using this expression on multi-row formula, but i am getting.

iif (IsNull([Row-1:Time]),[Row-1:Time],([Row-1:Time]+[Row-1:Time])/2)

what am i doing wrong?

[Luke_C]

Hi @rrahu07 

Calculating the average of times is tricky. You're getting an error because when you use addition it's expected to be used with numbers, not dates or text (not clear how you have your column formatted). Take a look at the below post on the topic:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculating-the-average-of-the-time/td...

[IraWatt]

Hey @rrahu07,

Not sure how you would calculate average time but I gave it a go here:

I did it separately for both Name groups.

Any questions or issues please ask :)
HTH!
Ira

[RolandSchubert]

Hi @rrahu07 ,

to calculate date and/or time, you have to use DateTime functions like DateTimeDiff to calculate a difference and DateTimeAdd to add half the difference to the first time. The problem is, that DateTime functions often expect Date AND Time, so you have to provide both or add a date to all time value. 

In the sample below I combine a date (2022-01-01) with all time values provided, calculate the difference between the rows (grouped by the Name field to separate days), and add half the difference to the first time.

Last step is to "re-convert" datetime to time only. 

Let me know if it works for you.

Best,

Roland

[rrahu07]

@RolandSchubert is there anyway to convert the average to sec so that I can compare the actually time difference?

[grazitti_sapna]

Hi @rrahu07, here is my solution to the problem to add to your point that you do not want to consider combining the last entry of the day with the next entry of the day for that you would need to provide the date along with the time field.

But you can try my workflow if it helps you to calculate the avg time in seconds.

I hope this helps!

Thanks!

[RolandSchubert · Accepted solution]

Of course, calculation of seconds is used to find the "average point in time", so you only have to modify the formula and change the data type for the result for "time" to numeric. 

DateTimeDiff([Time_for_Calculation],[Row-1:Time_for_Calculation], 'seconds') / 2

[rrahu07]

@grazitti_sapna @RolandSchubert THANK you for your help :)

[rrahu07]

@RolandSchubert one issue. Sorry to bother you again.

Time_for_Calculation	Average
2021-03-01 17:03:12	781
2021-03-01 17:07:42	135
2021-03-01 17:12:11	135
2021-03-01 17:22:17	303
2021-03-01 17:25:56	110
2021-03-01 17:59:48	1016
2021-03-02 09:04:30	27141

how can i make sure it is not averaging last time of 21-03-01 and 21-03-02?

[rrahu07]

@grazitti_sapna I used your solution. and getting the highlighted error. How can make sure I am not averaging last time of day 1 with first time of day 2.