Getting averages per minute, per hour, per day

[Shambhavi]

Hello.
I have 2 columns - event_timestamp (YYYY-MM-DD HH:MM:SS) and Total.
Event timestamp clearly means the time at which the event was triggered and Total is a Number of Operations performed by the event.
I would like find averages like operations per minute, per hour, per day and this should be selectable from a dropdown. Then using filters I can select a date range

Example : Operations per minute over last 10 days
Operations per day over last 15 days

So on.
How can I achieve this?

[Kelseykl]

Hi @Shambhavi, I believe I have created something similar to this before. I have added a simplified version below that might help you achieve what you are looking for:

All visuals contain Dummy data

1. Create a parameter to capture the granularity selected (Hour, Minute, Day, etc.), and set which one you would like to be the default under “Static Default Value”:

Create Parameter

2. Create a control from the parameter to allow user to select the granularity they would like, and define the granularity fields here (Hour, Minute, Day, etc.) :

• Be sure to select the box next to “Hide Select All option …”

Create Parameter Control

3. Create a new calculated field to display the corresponding granularity time values based on the granularity the user has selected:

Create Calculated Field

4. Add the calculated field and value attribute you would like to take the average of to a Pivot Table:

Create Pivot Table

• Set the calculated field aggregate to the lowest datetime granularity you would like to see:

  Update datetime aggregate

  

• Set the value attribute aggregate to Average:

  Set value to Average

  

• You can update the row name to reflect the granularity value selected (Ex: minute) by going to “Visual Properties” (select the pencil icon on the top-right of the visual). Then under “Row Name” → “Title” select the icon to the right of the text box and select the parameter created in step 1. Also, be sure to select “Tabular” from “Pivot options” → “layout”:

  Visual Properties

  

  Edit Row Name

  

  Select Tabular

  

5. Add a date filter based on the date attribute, not the calculated field:
   There are multiple ways you can do the date filter, this is just one example:

• Add the filter

  Add Date Filter

  

• Add a control from the filter, by selecting the ellipses and “Pin to Top”:

  Add Control

  

• You can edit the control by selecting the ellipses and “edit” from the control:

  Edit Control

  

• You can change the name of the control to reflect the granularity value selected (Ex: minute) by selecting the ellipses next to Title and selecting the parameter you created in step 1 from the list:

  Update Control Name

  

6. The steps above should allow you to select the granularity at which the values are averaged over, and filter for specific datetimes:

Minute Example

Hour Example

Day Example

Hope this helps! Feel free to reach out if you have any questions.

[Shambhavi]

Woah! Thank you! I am going to try this approach. Super helpful answer.
Thanks a lot

[Shambhavi]

I want to get average of all these numbers that I have selected in the time range.
Example MARCH 01, 2024 12:00AM = (3 + 13 + 59 )/ 3 = 25
MARCH 01, 2024 12:01AM = (1 + 2 + 4 + 5)/4 = 3
If I take Average (TOTAL) column , the output it gives is not correct.

[Kelseykl]

Hi @Shambhavi, that is interesting. Do mind switching average to sum, and sharing another screen-shot?

[Shambhavi]

I got this to work. The issue for skewed averages was because of 0. Some events had TOTAL = 0. I filtered those events out and I am getting the correct results now. Thanks a lot

[Kelseykl]

@Shambhavi, that is great! I am glad to hear you were able to get it to work!

If my suggestion helped you in resolving your question, can you mark the post as “Solution”. This will help the community to find guidance and answers for similar questions. Thank you!