9 Replies Latest reply on May 2, 2017 9:54 AM by Shinichiro Murakami

# How can I calculate Average Time (00:00:00:00)?

Hi,

I had a hard time to get an average time on my report.

Here's the challenge part:

- The time difference (5th column - image below) was calculated if there's any succeeding row for the same clientID. If no subsequent, no calculation necessary and 5th row result in 0.

- If there's a calculation and a DATEDIFF, I'm looking for the average per day (disconnected_at)

- Once average found, not sure what's the best visualization to present this results - initially thought on a single bar. I attempted to do so in my sheet2. However, I'll be more than happy to hear any recommendation for a visualization in this condition.

Here's a screenshot

I'm looking for a calculation that would give me on 3/6 the average time of 01:29:37 (per image below)

Ps. formation 00:00:00 is important

Could someone help me understand what am I doing wrong on sheet2 for calculation and visualization?

I truly appreciate any help on this one.

Many Thanks

• ###### 1. Re: How can I calculate Average Time (00:00:00:00)?

Hi Thiago

This is tricky one.

// Keep Null on datediff

[DATEDIFF]

DateDiff('second',lookup(min([Disconnected At]), -1),min([Connected At]))

// avg score by "day"

[Window_avg]

window_avg([DATEDIFF])/(60*60*24)

// format alignment

[Display win_avg]

right(str(100+int([Window_avg]/24)),2)+":"

+right(str(100+datepart("hour",datetime([Window_avg]))),2)+":"

+right(str(100+datepart("minute",datetime([Window_avg]))),2)+":"

+right(str(100+datepart("second",datetime([Window_avg]))),2)

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: How can I calculate Average Time (00:00:00:00)?

That was a genius calculation, Shinichiro Murakami ! Thank You! I learned a lot from your reply.

Do you think you could help me with the visualization part of my question?

The goal was to represent the average per day. I update the table calculation fields to calculate the average per day (sheet1 copy). But I did not succeed creating a visualization (single bar, or any other) on sheet2

I'm attaching my the same sample file with the Table Calculation. I would highly appreciate if you could give me your input on this part.

Thank You,

Thiago

• ###### 3. Re: How can I calculate Average Time (00:00:00:00)?

Thiago,

Table calculation is VERY complicated.

Anyway, something like this?

Window_avg for ID

[Avg per ID]

window_avg([Window_avg])

and filter only one per ID by index filter.

[index filter]

index()

Thank,s

Shin

• ###### 4. Re: How can I calculate Average Time (00:00:00:00)?

Thank You, Shinichiro Murakami

The bar chart is exactly what I was seeking to achieve.

However, the ideal was an average per day, not per ID (as per sheet1 copy).

I'm trying to make modification on your file but no sucess. Do you think it's because each day is attached to many variants (like; id and clientid)?

Can you help me?

Thank you so much in advance

• ###### 5. Re: How can I calculate Average Time (00:00:00:00)?

I'm a bit confused.

The base calc of Datediff is based on ID or Client ID, I believe.

And what do you mean by Day average?

Day x ID / or Day x Client ID ?

Or on top of Day x ID / or Day x Client ID , Daily average calculation is needed?

Thanks,

Shin

• ###### 6. Re: How can I calculate Average Time (00:00:00:00)?

Hi Shinichiro Murakami thank you so much for helping me with this.

Yes, DATEDIFF is based on ClientId. Basically, in case the clientID has a subsequent connection (2nd row) - then we calculate the datediff of the connection_at time and prior disconnected_at. If no subsequent row, there's no calculation - NULL.

The ID is necessary as the clientIDs are subgroup the ID itself. It's possible that same clientID shows up on another ID. For this reason, ID was kept in the table calculation. However, you are correct; the datediff is based on clientID.

Daily average, I meant as win_avg per day. As per image below,

I'm looking for a visualization that represents the table on the pic above.

Y-axis: Display win_avg

X-axis: DAY(Disconnected_at)

Do you think this bar chart is possible?

Thank You so much

• ###### 7. Re: How can I calculate Average Time (00:00:00:00)?

Thiago,

This one?

Thanks,

Shin

• ###### 8. Re: How can I calculate Average Time (00:00:00:00)?

That was genius Shinichiro Murakami !

Thank You so much

• ###### 9. Re: How can I calculate Average Time (00:00:00:00)?

Thiago,