6 Replies Latest reply on Nov 24, 2018 2:11 PM by Chris Chalmers

    How to convert continuous range of dates start and end date into discrete dates

    Kunal Arora

      Here, Rm Blk Beg Dt and Rm Blk End Dt are shown in ranges.

       

      1. Lets take first row :

       

      Rm Blk Beg Dt     Rm Blk End Dt   Measure Value(Hardblock)

      2/1/2005                    12/31/2006     15

       

      can this be broken into

      Date        Measure Value(Hardblock)

      2/1/2005 15

      3/1/2005  15

      4/1/2005  15

      .....

      31/1/2005 15

      1/2/2005 

      ....

       

       

      12/31/2006  15

       

      Like this, I am looking for daily, monthly view. If possible, please let me know and guide me.

        • 1. Re: How to convert continuous range of dates start and end date into discrete dates
          Okechukwu Ossai

          Hi Kunal,

           

          Your data is not well structured and needs some transformation. Tableau Desktop is a visualization/analytics tool and so is not primarily designed to transform your data. This should be done prior to feeding the data into Tableau. Depending on your data source, a basic ETL pipeline will help you to do this using Python, VBA or other mainstream ETL packages.

           

          Hope this helps.

          Ossai

          • 2. Re: How to convert continuous range of dates start and end date into discrete dates
            Chris Chalmers

            Hey Kunal,

             

            Tableau Desktop has limited data preparation capabilities compared to tools dedicated to that task, like Ossai said, but depending on what visualizations you are trying to create, there may be ways to do it entirely within Tableau. If you can be more specific about what you're trying to create, we may be able to propose specific strategies.

             

            There is one strategy in particular that I've found useful for visualizing duration data: you can pivot your data and apply something called Data Densification. I'm not sure if it will do exactly what you need, but it's worth a shot.

             

            Pivot + Densify

            This strategy is easiest to demonstrate on data with a unique ID field. I couldn't find one of those in your data, so I made some dummy data. You may not need a unique ID for what you're doing.

            BeforePivot.PNG


            Pivot on Start Date and End Date (select both, right click, and select "Pivot") and do some renaming.

            AfterPivot.PNG

             

            Now make a viz like this:

            BeforeDensification.PNG

             

            Right click on the "DAY(Date)" pill and select "Show Missing Values". This is the Data Densification part. Selecting that option creates marks for every date on the view, and table calcs can act on that data. To demonstrate how this is useful, create these calculations:

            DateRangeAccumulator.PNGRunningSumOfAccumulator.PNG

             

            Drag "Running Sum of Accumulator" to the Filters shelf and filter out the zeros. You'll get something like this, showing the duration of each row with one mark per day.

            AfterDensification.PNG

             

            You can play around with table calcs to create various visualizations using this "densified" data. For example, this shows how many rows span a certain date:

            NumDurationsSpanningDays.PNG

             

            Applying this strategy to your data, here's a viz of the total number of block rooms since 2008, by day (assuming I understand your data correctly).

            HotelBlocks.PNG

             

            I've attached both of the above workbooks.

             

            I hope this helps. If it doesn't, let us know what kinds of visualizations you're trying to make, and we can offer more tailored advice.

             

            -Chris Chalmers

            • 3. Re: How to convert continuous range of dates start and end date into discrete dates
              Norbert Maijoor

              HI Kunal,

               

              In addition to all mentioned above by Chris Chalmers & Okechukwu Ossai you could also evaluate the capabilities of 'scaffolding' and investigate if it's supports your scenario. It is explained in several links here

               

              Hope it helps

               

              Regards,

              Norbert

              • 4. Re: How to convert continuous range of dates start and end date into discrete dates
                Kunal Arora

                Thanks all.

                 

                In the attached excel file available in the link; there are different columns for each KPI; like block room and booking demand date. Is there any way to

                ,https://drive.google.com/drive/folders/17wU4lbuS2f5ELYa5ynWsqJ0SREXUGM_g?usp=sharing

                 

                Please confirm if its possible to create one calculated field that can be linked to all different fields available in different tables like start and end date. In relation to this date field, I would like to see Demand, Hardblock, Tax and Spoilage (Hardblock-Demand).

                • 5. Re: How to convert continuous range of dates start and end date into discrete dates
                  Chris Chalmers

                  Hey Kunal,

                   

                  The names of the columns in your data set are very hard to understand. You may get more responses if you upload a version with the important columns fully spelled-out.

                   

                  -Chris Chalmers

                  • 6. Re: How to convert continuous range of dates start and end date into discrete dates
                    Chris Chalmers

                    After spending some quality time with your data, I think I understand what you're asking. For the data densification approach, you can incorporate as many duration-based metrics as you want on a single viz by pivoting on all the relevant date fields. You'll end up with two columns: the Date itself, and a Date Type, where the date types are your column names, e.g.:

                     

                    Hcrt Cst Beg Dt

                    Hcrt Cst End Dt

                    Htl Tx Beg Dt

                    Htl Tx End Dt

                    Rm Blk Beg Dt

                    Rm Blk End Dt

                     

                    Then you design "accumulator" calculations (that's what I call them, not an official name) for each duration. And with the added flare of a table-calc-based date filter, you can create visualizations like this:

                     

                    Timeline.PNG

                     

                    To make this work with densification, the date field must be in the LOD of the viz. It is therefore difficult (maybe impossible?) to use any of these duration-based metrics as dimensions. You'll have better luck with scaffolding if that's what you need. You can, however, hide the date LOD from the visual presentation of the viz by:

                    1. Put Date onto the Detail shelf instead of Rows/Columns
                    2. Force table calcs to run along Date, and
                    3. Use window functions and the index function to aggregate up from one-mark-per-day to one-mark-per-category.

                    This allows you to show values computed using the timeline without seeing the timeline, like this:

                     

                    CityComparison.PNG

                     

                    -Chris Chalmers

                    1 of 1 people found this helpful