Hierarchies with Time Dependency - Slowly Change Dimesion Hierarchy
We are facing some issues when we are trying to use in a Qlik Sense app script hierarchies with Time dependency, using a slow change dimension.
Let me try to describe in few words the issue:
There is a table (EmployeesOrg_Assignment) which represents the assignment of employees to organization units over time – each employee assignment has a Position Start and End Date (slowly changing dimension)
There is also a table ([OrganizationDYN]) which represents the hierarchy structure of Organization units over time each Organization units has a reference to Parent with Start and End Date (slowly changing Dimension as well), is an unbalanced hierarchy, 8 levels depth, an Adjacency list model hierarchy (NodeID, ParentId, Name)
There is a fact Table (Timesheets_Data) which has the hours consumed by Employee
The goal is to allocate the consumed hours of each Employee to the appropriate organization unit based on fact transaction date (fact date between Position Start and position End Date)
and also to the organization Hierarchy structure (fact Date between Level Start and Level End Date) for each level.
There is a post ( https://community.qlikview.com/thread/190258) which I think mention the same case, the awnser of HIC was to combine the hierarchy resolution with model for a slowly changing dimension, we tried with no luck so far,
The allocation of hours doesn’t work correctly when there is a node with two possible parents, the example appears in application 2nd Sheet.