Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you post a screenshot of your datamodel from the Data Model Viewer?
-Rob
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
I don't see any links between the tables?
-Rob
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)
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!!!