Qlik Community

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:

PersonIdAllocStartDateAllocEndDateProjStartDateProjEndDate
100112/1/201312/1/20141/1/201411/30/2014
10012/1/20152/5/20152/1/20152/5/2015
100212/1/201312/1/2014--
10022/1/20152/5/2015--
10032/1/20152/5/2015--
100512/1/201312/1/2014--
1005--1/6/20157/6/2016
1003--1/5/201312/31/2014
1005--2/2/20163/31/2016
10051/2/20161/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-13Jan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14Nov-14Dec-14Jan-15Feb-15
Count(PersonId) - based on AllocEndDate1089642569539371341335291259244239238237145
Count(PersonId) - based on ProjStartDate1704731292929292929292929292912
Count(PersonId) - based on ProjEndDate20220119917612110482696353505047313

Please suggest how to handle multiple dates and represent all unrelated dates in a single chart with single date dimension.

2 Replies
MVP & Luminary
MVP & Luminary

Re: Handling differrent dates for calculation

Hi,

Try canonical date concept, refer below link

Canonical Date


Regards,

Jagan.

Highlighted
sebastiandperei
Valued Contributor

Re: Handling differrent dates for calculation

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