Handling differrent dates for calculation

[#1]

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.

[jagan]

Hi,

Try canonical date concept, refer below link

Canonical Date

Regards,

Jagan.

[sebastiandperei]

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))