1 of 1 people found this helpful
In order to achieve this, you need to create a Master List of Dates that can be joined on. In Tableau, we can create new "Columns" through the use of calculated fields, duplicated fields, etc, but we cannot create new Records (Rows).
Here is an example using Excel, the concept would be the same if you are using a database, you would just need to create a Table, or a View.
I hope this helps.
First, thanks and I got good results at the "month" level but not at the year level and quarter level. One thing I did notice is that this required an extract doe a "countd". I have a concern with the process. In my test sample there were three records, after the join to materdate, I now have 89 rows.
The number of records I am going to be working with will be 2.8M, if this gets compounded at the same ratio, I'll end up with almost 83M records.
Please advise. We are in the design of a data warehouse table so we can add at this time.
I hope I did the attachments correct.
In order to report on a granular level, we have to materialize granular data somewhere, which means we are going to have more records and more space.
If the latter is your concern (And the data is simple, like doing a count of customers), I would consider minimizing your Date table to the smallest level of detail you're going to report on. So if you know that you are only going to show a Viz at the level of Month and up, there is no need to materialize every single day that falls between the dates.
You could simply Truncate your Start and End Dates to the Level of Month, and then cross join it (With where clause) against a date table with months.
Also, instead Joining the tables in Tableau, I would consider creating a View or a physical table in your DB that you can easily connect to in Tableau.
That being said, I am a big believer in having as granular of data as possible. This ensures I have the most flexibility in Tableau to discover insights. Pre-aggregations can lead to miss-informed Dashboards if not properly handled. Space is cheap and Tableau does a good job of handling large datasets, so go granular whenever you can.
I hope this helps.
Let me know if you have any more questions.