Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
We have a single huge fact table which contains data of HR system. Columns are PersonId, Allocation (to any department) Start Date, Allocation (to same department) End Date, Project Start Date, Project End Date. There could be multiple entries for single PersonId, as once a project or allocation ends a new row of same personId added with different allocation and/or project dates.
Example of data:
PersonId | AllocStartDate | AllocEndDate | ProjStartDate | ProjEndDate |
1001 | 12/1/2013 | 12/1/2014 | 1/1/2014 | 11/30/2014 |
1001 | 2/1/2015 | 2/5/2015 | 2/1/2015 | 2/5/2015 |
1002 | 12/1/2013 | 12/1/2014 | - | - |
1002 | 2/1/2015 | 2/5/2015 | - | - |
1003 | 2/1/2015 | 2/5/2015 | - | - |
1005 | 12/1/2013 | 12/1/2014 | - | - |
1005 | - | - | 1/6/2015 | 7/6/2016 |
1003 | - | - | 1/5/2013 | 12/31/2014 |
1005 | - | - | 2/2/2016 | 3/31/2016 |
1005 | 1/2/2016 | 1/5/2016 | - | - |
I want to count the number of PersonId based on different dates and reperent it in a Pivot and Bar chart, against to single Date dimension (Month), for example:
Dec-13 | Jan-14 | Feb-14 | Mar-14 | Apr-14 | May-14 | Jun-14 | Jul-14 | Aug-14 | Sep-14 | Oct-14 | Nov-14 | Dec-14 | Jan-15 | Feb-15 | |
Count(PersonId) - based on AllocEndDate | 1089 | 642 | 569 | 539 | 371 | 341 | 335 | 291 | 259 | 244 | 239 | 238 | 237 | 14 | 5 |
Count(PersonId) - based on ProjStartDate | 170 | 47 | 31 | 29 | 29 | 29 | 29 | 29 | 29 | 29 | 29 | 29 | 29 | 29 | 12 |
Count(PersonId) - based on ProjEndDate | 202 | 201 | 199 | 176 | 121 | 104 | 82 | 69 | 63 | 53 | 50 | 50 | 47 | 31 | 3 |
Please suggest how to handle multiple dates and represent all unrelated dates in a single chart with single date dimension.
First, add this in your script:
aux_dates_table:
Load Distinct
AllocStartDate as aux_date
Resident fact_table;
Concatenate
Load Distinct
AllocEndDate as aux_date
Resident fact_table;
Concatenate
Load Distinct
ProjStartDate as aux_date
Resident fact_table;
Concatenate
Load Distinct
ProjEndDate as aux_date
Resident fact_table;
(make sure "aux_date" field name are not used yet, because these table need to be disconnected from the fact)
Now, you can create a chart with dimmention:
MonthName (aux_date),
and expressions like:
Count (Distinct if(aux_date=AllocStartDate, PersonId))
Count (Distinct if(aux_date=AllocEndDate, PersonId))
Count (Distinct if(aux_date=ProjStartDate, PersonId))
Count (Distinct if(aux_date=ProjEndDate, PersonId))