1 Reply Latest reply on Nov 22, 2018 11:19 AM by Don Wise

    Min value for every hour by weekday including hours with no records | Difficulty with aggregation

    Jim vd Velde

      Hi all,

       

      I am looking for a Min (and Max) number of calls in my dataset on hourly level by weekday. My number of calls = COUNTD(CallID) so it is aggregated.  My time dimension format is dd-mm-yyyy hh:mm:ss. The period is October 2017/September 2018, all days have records.

       

      Right now, the problem I’m facing is that I have combinations of weekdays/hours where there simply are no calls. So, when I display my hours Tableau only shows the hours where there are calls and the minimum then is 1.

      What I want is also show the hours where there is no data, in that case de minimum should be 0. I should always see the full hourly time range for each weekday.

       

      So, what I want is first on my rows the weekdays, followed by hours (0/23 > workbook locale Dutch) and on the Text Marks my Min and Max calls (I’m making a Text Table).

       

      Unfortunately, due to privacy I have no workbook/dataset available for you guys. I hope I made myself clear and someone can help me and others since I couldn’t find an answer online. I work with Tableau 10.3.

       

      Kind regards,

      Jim

        • 1. Re: Min value for every hour by weekday including hours with no records | Difficulty with aggregation
          Don Wise

          Hi Jim,

          If you look at the below screenshot, my example of counting timestamps, you'll see that if I'm bringing in NULL values Tableau will display them if 'Show Missing Values' at the lowest level of granularity (hour in this case) is checked. 

           

          Because there is no data, my initial result was the hour would display with simply blank space.  So there'd be a value for 1am and 2am but for 3am it was simply blank.  To show '0', wrap your calculation with ZN like this: ZN(COUNT([Timestamp])) to force Tableau to display a '0'.  Hope this helps! Thx, Don

           

          Screen Shot 2018-11-22 at 11.15.44 AM.png