Problem around showing a count of 2 different things through using one date in a chart

[helen_pip]

Dear Qlik experts

I was wondering if someone could kindly help me with achieving a stacked bar chart which will show the week commencing in which an ID had a start  date, but will also show when an ID was removed

Example being that and ID has a start date for WC 10/02/2020 and would therefore be counted in a stacked bar chart under 10/02/2020, but the ID was removed in WC 08/06/2020 and would therefore be counted as 1 in WC 08/06/2020

The problem is I can only show one date in the X axis of the stacked bar chart but the counts are using Start Date and Removal Date....

I have a created an initial table which has all the fundamentals I need....I just need help with the next step I.e. how do I get a start and removal count for one ID to show in the correct weeks?

Any help is appreciated

Table1:

Load

ID,

Start Date,

Removal Date,

Week_By_Date_Start,  //This is the Monday which the StartDate falls in

Week_By_Date_End,    //This is the following Sunday which the StartDate falls in

Week_By_Date_Removal_Start,  //This is the Monday which the Removal Date falls in

Week_By_Date_Removal_End    //This is the following Sunday which the RemovalDate falls in

Count( Distinct (If([Start Date] >= Week_By_Date_Start and [Start Date] <= Week_By_Date_End, [ID]))) as Count_ID_Adds_By_Week, //Counts when the ID has a Start Date

Count( Distinct (If([Removal Date] >= Week_By_Date_Removal_Start and [Removal Date] <= Week_By_Date_Removal_End, [ID]))) as Count_WL_Removals_By_Week
//Counts when the ID has a removal Date

Resident

Table X
Group By
ID,

Start Date,

Removal Date,

Week_By_Date_Start, 
Week_By_Date_End,   
Week_By_Date_Removal_Start, 
Week_By_Date_Removal_End

[JeromeS]

Hi Helen,

One way to achieve what you want would be to build a canonical calendar in your script (see post https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578)

It would look like this:

 DateBridge:
     Load
          ID,
          [Start Date] as CanonicalDate,
          'Start Date' as DateType
     Resident Table1;

 Concatenate(DateBridge)
     Load
          ID,
          [Removal Date] as CanonicalDate,
          'Removal Date' as DateType
     Resident Table1;

In parallell to DateBridge Table you can also add a Canonical Calendar which will hold all the different useful calculated time fields for Canonical Date (year, week, ... see for example the week function here https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTi...)

Once constructed, you can use the Canonical Date or Week in your stacked bar chart as a dimension. Your measure will then become: 

Count({<DateType = {'Removal Date', 'Start Date'}>} Distinct ID)

If you want to have different colors for removal Date and Start Date, just create two measures as follows:

Count({<DateType = {'Removal Date'}>} Distinct ID)

Count({<DateType = {'Start Date'}>} Distinct ID)

Hope that helps !

Kind regards,

Jérôme