2 Replies Latest reply on Jun 27, 2018 1:53 PM by Santiago Sanchez

    How to display all of past seven days with sparse date data

    Gilad Wilkenfeld

      Hello all. I have a question about a viz I am trying to create that shows all the events that have occurred over the past seven days. The date field is sparse and the only way I can show all seven days right now is to fix the x axis. This is obviously not a good solution as the data should update every day. I cannot share the workbook due to privacy considerations, but I have attached a picture of the viz the way I would like it to look automatically. The other problem is that I would like to create two reference bands, one for the last 24 hours and one for the last 72 hours. Again, I don't seem to know a calculation that can force these roving time periods.


      Thanks for any and all the help.

        • 1. Re: How to display all of past seven days with sparse date data
          Jeevan Krishna

          Hi Gilad,


          Change your date field to continuous field and tableau will fill all the missing dates since its a continuous field.


          For a sample data like this

          In the tableau dashboard it looks like this with discrete and continuous fields.

          This shows only the dates that are available in the excel shown above. If you change your date field from discrete to continuous, tableau fills the spaces in middle.



          One problem with this is this view ignore the data being unavailable at some dates (example Jan 3). This may give false interpretation to users. Use ISNULL to convert missing data points to either 0 or NULL to show exact data.


          For the reference band, you need a min and max value.

          So create them using these calculations,


          The flower braces around the calculation means they are fixed to overall table. And these 2 calculated fields provide max and min ranges of the reference band as your Maximum date and its previous day.


          Convert these fields to continuous fields and add them to the detail mark.Use them in creating your reference band.



          Similarly, create for 3 days and use them to create another band.

          1 of 1 people found this helpful
          • 2. Re: How to display all of past seven days with sparse date data
            Santiago Sanchez

            Hi Gilad,


            I took a similar approach as Jeevan except I used a calculated field for TODAY which is used in a dual axis. This helps when there's no data for the last day and you still want to keep the reference bands. The workbook is attached along the calculations.



            Hope this helps!


            1 of 1 people found this helpful