4 Replies Latest reply on Sep 12, 2016 1:51 AM by Isaac Mickey

    Limit Hourly Results to Once per Day

    William Kenworthy



      I have a simple workbook with hourly data (regional electric load in MW) for the year. 


      I've created a horizontal bar chart that shows hourly load and sorted from largest to smallest.


      But the results return more than one result per day in the top results.  I need to show the top 5 hours only with only one result per day, so for example in the above chart, the September 6 at 4pm result needs to be filtered out since the September 6 at 5pm result already shows up.


      I found that by changing the rows timeframe to days, it gives the correct answer:


      The problem is that it only shows the days, I need to label the hours and can't figure out how to show the hours.  I'm enclosed with workbook extract.  Please help! 



        • 1. Re: Limit Hourly Results to Once per Day
          Veronica Simoes

          Hi William,


          If I understood, you only need to format the dimension date end time.



          2 of 2 people found this helpful
          • 2. Re: Limit Hourly Results to Once per Day
            William Kenworthy

            Thanks for replying, it looks like the view that you posted still has the results from the same day.  For example, the first two results (21,175 and 21,087) occurred on August 11 at 3pm and 4pm respectively.  What I want to do is filter out other hours on the same day, so the August 11 at 4pm and August 11 at 2pm (and any other August 11 hours) would be removed.   


            It works if I use the day filter (as in the second screenshot), but it won't show me during what hour that occurred.

            • 3. Re: Limit Hourly Results to Once per Day
              William Kenworthy

              To further clarify, the results in my third screenshot are correct, I just want to show what hour of the day those daily maxes occurred.  The problem seems to be that since it's daily max, just formatting the axis with the hour returns 00:00:00


              Google ChromeScreenSnapz001.png

              • 4. Re: Limit Hourly Results to Once per Day
                Isaac Mickey

                Hi William


                What about doing a DATEPART('hour',[Date and Time CPT]?  This will simply strip out the hour from your date/time field and then you can use it in a variety of ways;


                1. You use your DAY version of the max, hide the axis and create a string label with a calculated field using DAY[Date and Time CPT] and the Hour DATEPART
                2. You simply add the hour value to your tooltip
                3. you could even colour code your bars based on the hours they occurred

                I don't have Tableau 10 so I can't show you with your example but I've done it with some data that I have with date & time in it.


                Hope this helps