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.
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.
Pivot on Start Date and End Date (select both, right click, and select "Pivot") and do some renaming.
Now make a viz like this:
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:
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.
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:
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).
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.
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
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).
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.
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:
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:
- Put Date onto the Detail shelf instead of Rows/Columns
- Force table calcs to run along Date, and
- 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:
cchalmers_Hotel_v2018.1.twbx 2.9 MB