10 Replies Latest reply on Aug 11, 2017 3:54 PM by Shinichiro Murakami

# How to select and display a value in dataset based on hour of day?

Hi, I have a simple dataset with 2 columns: column 1 is hour of day, and column 2 is coefficient values corresponding to each entry in column 1. I'd like to select and display the coefficient value based on the current hour of day, e.g., using:

IF DATENAME('hour',NOW())= '11' THEN _____ END

I'd like to know what should be the "_____" above so that it'll display 1.1 (based on the dataset) when the hour of day is 11.

If there's another more elegant way that doesn't involve hard-coding the "='11'", that'd be even better.

To give a little more context, there will be several values that will need to be obtained this way and the values will be plugged into a regression equation. The underlying dataset will be updated hourly and the dashboard/calculation will be refreshed hourly.

Thanks!

Regards, Tze

• ###### 1. Re: How to select and display a value in dataset based on hour of day?

Hi Tze,

I was in time  to verify 11:00 case.

Thanks,

Shin  << Ambassador Spotlight Here ! >>

• ###### 2. Re: How to select and display a value in dataset based on hour of day?

You could either split this field at "hour" or just parse the string (which is what I've done in my example) - it shouldn't much matter in a dataset this small. Try 2 calculations. The first we'll call Hour:

INT(TRIM(REPLACE([Variable Name],'hour','')))

This replaces the text 'hour' in the Variable name with an empty string, effectively cutting it out of the field altogether. I then trim it to remove leading and trailing spaces, though you could change the original function to replace the text 'hour ' (notice the included trailing space). I just prefer this way because I make sure I get all the whitespace. I then cast it to an INT so I can compare it directly to the current hour.

Your Value calculation would now be changed to:

IF DATEPART('hour',NOW())= [Hour] THEN [Coefficient]

END

• ###### 3. Re: How to select and display a value in dataset based on hour of day?

Too fast, Shin!

• ###### 4. Re: How to select and display a value in dataset based on hour of day?

Ben,

I just wanted to verify 11:00 exactly in my PC time.  I only had a few minutes before noon.

Thanks,

Shin << Ambassador Spotlight Here ! >>

• ###### 5. Re: How to select and display a value in dataset based on hour of day?

Thanks Shin!

Here's a follow-up question...

Let's say I want to display the coefficient value 6 hours from now, e.g., it's 320pm EST and I want the dashboard to show the coefficient at 9pm (coeff = 2.1); at 4pm EST I want to show the coeff at 10pm (2.2), etc... how would you modify the formulation?

I tried to do a "+6" in the equation but that'd be mixing string and integer. Any thoughts?

Thanks again.

Regards,

Tze

• ###### 6. Re: How to select and display a value in dataset based on hour of day?

Hi Tze,

You can shift "now() " by 6 hours.

Thanks,

Shin << Ambassador Spotlight Here ! >>

1 of 1 people found this helpful
• ###### 7. Re: How to select and display a value in dataset based on hour of day?

Thanks again Shin.

Another follow-up question...

Now that I got a value based on time of day shifted by 6 hours (as indicated by [HourValue6HoursLater] in the attached file), and a value based on day of week (as indicated by [DayOfWeekValueTmrFinal] in the attached file), I want to multiply them together.

I created a new Measure called [RegressHourDayCalc] to multiply the sum of one with the sum of the other (otherwise I'll just get 0 if I don't use some sort of aggregate), but the result is slightly off.

Based on the time and the day here (4pm), coeff for time of day shifted by 6 hours is 2.1, and coeff for day of week (Friday) is 1.6. Instead of getting 3.36, I'm getting 3.520.

Any thoughts on why that's the case and how to fix it?

Thanks again.

Regards,

Tze

• ###### 8. Re: How to select and display a value in dataset based on hour of day?

Tze,

4 Pm 6 hours shifted should be 22:00.

It's 3.52

Thanks,

Shin << Ambassador Spotlight Here ! >>

1 of 1 people found this helpful
• ###### 9. Re: How to select and display a value in dataset based on hour of day?

You are right!!!

I ran into yet another issue...

Aside from displaying the +6h coefficient, I also need to do +7, +8, ... +12. However, when it comes to grabbing the coefficient corresponding to midnight, Tableau gives it a "0" value instead of 2.4. Is it due to the way midnight hour is represented in Tableau?

I tried several variable names for midnight in my dataset including "hour24", "hour00", "hour0", and "hour". But none of them show the value of 2.4.

Any insights?

Thanks again.

Regards,

Tze

• ###### 10. Re: How to select and display a value in dataset based on hour of day?

Convert 0 to 24.

Detail

Thanks,

Shin << Ambassador Spotlight Here ! >>