Forum Discussion
Apply Custom Date Hierarchy to Model
I'll try to give Kudos accordingly to all those who have helped but ultimately a combination of fixes were necessary:
1. Ensure there exists a Date table within the model that includes its own hierarchy with "Week" within it.
2. Separate the table of data into 2 tables, one for opened items and one for closed items
3. Ensure each table's Date column is either formatted as "Date" (rather than DateTime) or else create a duplicate column that is defined as Date only
4. Connect the two tables' "Date" formatted Date columns to the Date table within the model
5. Build the Visualisation, using the Count of the date only date columns of the two data tables as the column values, and the Date hierarchy as the shared axis - the data has come out perfectly.
Hi PantherusNZ ,
The date hierarchies will not work when there is one relationship(with multiple cardinality) on that field. Please review the content in the following links and check whether the methods in these links can help you resolve your problem.
Auto Date Hierarchies Gone after Adding Table/Join
Best Regards
Hello, thank you for your reply and links.
I've read through those discussions but don't understand how to apply the advice to what I'm trying to do. I had created the new Date table because I wanted to be able to add Week to the hierarchy, with it in place, even when marked as the date table, the hierarchies still are only Year/Quarter/Month/Day - then if I apply the relationship to try and actually connect them the hierarchy vanishes. So clearly I'm doing it wrong but I don't understand from those links what to actually do...