8 Replies Latest reply on Mar 7, 2016 12:01 PM by Nicholas Hara

# Minimum of hourly data when data points are minute samples

Hello All,

I have a data set that is measured in minutes and I need to find the minimum of the hourly avg values for each day. Currently, the MIN function finds the minimum value of minute data set for the day, rather than the desired hourly avg minimum value.

Would love any inputs on how to tackle this issue. Thanks!

• ###### 1. Re: Minimum of hourly data when data points are minute samples

Hi Dennis,

This question depends heavily on how tableau is reading your data, as well as the data structure.

Do you happen to have a sample that we can look at to help out?

• ###### 2. Re: Minimum of hourly data when data points are minute samples

Sure. Attached is a packaged workbook of the data that I have.

• ###### 3. Re: Minimum of hourly data when data points are minute samples

Like this?

Or did I misunderstand?

• ###### 4. Re: Minimum of hourly data when data points are minute samples

Hi Dennis

An LOD should get you the answer you are looking for.

1. Right-click on Sample Time> Create>Custom Date

2. Select Hours and Date Value

3. Go To Analysis>Create Calculated Field

4. Enter the formula: {INCLUDE [Sample Time (Hours)]: AVG([Measured])}

a. This calculated field will aggregate the average measured value at the Hour level.

5. Add Test to the view and change the measure to MIN.

I have attached a workbook that shows how this can be accomplished

• ###### 5. Re: Minimum of hourly data when data points are minute samples

Thanks Pooja and Nic. This is almost what I was looking for, but I realized that I am being very vague and did not exactly specify. Apologies.

The objective: To find the absolute minimum value of the hourly measured from minute samples. In the image below, @4pm the minimum measured between all the devices was -1.881. It is also the minimum for the entire day. I would like to capture that minimum for every day.

I figured that if I change the calculation of [Measured] to MIN, in the formula: {INCLUDE [Sample Time (Hours)]: AVG([Measured])}, it would solve the problem. If I do that I am back to square 1

• ###### 6. Re: Minimum of hourly data when data points are minute samples

Hi Dennis,

If I am understanding you correctly, you want the minimum value for ALL Measurement Devs for the given time period.

I changed the calculation to match what I think the desired result is:

{INCLUDE [Sample Time (Hours)],[Measurement Dev.]: MIN([Measured])}

I then changed the aggregation to Min. As you can see from the screenshot, the Grey line follows the minimum for the entire period:

• ###### 7. Re: Minimum of hourly data when data points are minute samples

Nic, you're awesome for helping me out, but I have to apologize once more because the figure I shared with you was in the wrong representation of the data.

If I convert minute samples to hourly average, the new minimum for the entire day becomes -0.669. This is new minimum I want to capture.

What is currently being captured is the minimum value of the minute sample which is -1.90. This is because the MIN function takes the minimum of the minute sample, rather than the hour samples.

So back to the objective state: To find the absolute minimum value of the hourly measured from minute samples.

• ###### 8. Re: Minimum of hourly data when data points are minute samples

Odd, both our numbers seem to be slightly off, but I think I have the answer:

Edit the calculation to:

{INCLUDE [Sample Time (Hours)],[Measurement Dev.]: AVG([Measured])}

Then add it to the view:

A good resource for understanding Level Of Detail Expressions can be found here:

1 of 1 people found this helpful