Table Association / Calendar

[nicouek]

Hello All,

I have a project I am working on and got stuck, I was wondering if someone could help? Thank you

I have 2 tables, a [HR _WRKF] table ,and [HR_TRNG] table. I am trying to determine what % of employees  took the training?

Answer --> [HR_TRNG]/[HR _WRKF]...fair enough

My issue is when I apply a calendar to aggregate each count by YEAR in the filter pane, one of the table ID count changes. I think my issue may be in the calendar association but I am not sure... If there is any insight you can provide please assist and if you need more info do not hesitate, please ask.

Thank you

[rwunderlich]

Can you post a screenshot of your datamodel from the Data Model Viewer?

-Rob

[nicouek]

Hello Rob

Sorry about the delay, had to cook up a data model since can't share the actual data.

Hope the image makes it... The WRKF_DATE is a common table developed by admins to kind of by pass a Master calendar creation, I don't like it but...  hope this helps. Thank you

[rwunderlich]

I don't see any links between the tables?

-Rob

[vinieme12]

You basically need to link all your tables in the model with the calendar

Step1) create a new field in HR_WRKF same as Emp_ID

Emp_ID as Employee_Id

Reason: We don't use key fields for aggregation, 

field Emp_ID will be used to link your TRNG and WRKF tables

Step2) Create a new table to consolidate Start_dt from HR_WRKF and completion_dt from HR_Training table into one table as below

LinkTable:

Load

Start_dt as JOINT_DT

,Emp_Id

,'StartDate' as DateType

Resident HR_WRKF;

Concatenate(LinkTable)

Load

Completion_DT as JOINT_DT

,Curr_emp_ID as Emp_Id

,'CompletionDate' as DateType

Resident HR_TRNG;

Step3) link the TRNG table to the LinkTable

Rename field Curr_emp_ID to Emp_Id;

Then in your charts use

count(Distinct Employee_Id)/count({<DateType={'CompletionDate'}>}Distinct Employee_Id)

[nicouek]

Good morning to all that replied to my thread

I would like to thank you for your time and consideration. My silence shouldn't be taken as ungratefulness. I applied some of your suggestion and still didn't get the desired results, however I have been able to resolve my problem with "IntervalMatch". Intervalmatch [Effective_From_DT] and [Effective_To_DT]; I think associating these two fields helped identified each CURR_EMP_ID count . Thank you very much again... Cheers!!!