# New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

## Handling differrent dates for calculation

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.

2 Replies
MVP & Luminary

## Re: Handling differrent dates for calculation

Hi,

Try canonical date concept, refer below link

Canonical Date

Regards,

Jagan.

Highlighted
Valued Contributor

## Re: Handling differrent dates for calculation

aux_dates_table:

AllocStartDate as aux_date

Resident fact_table;

Concatenate

AllocEndDate as aux_date

Resident fact_table;

Concatenate

ProjStartDate as aux_date

Resident fact_table;

Concatenate

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