Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
helen_pip
Creator II
Creator II

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

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

 

 

 

 

 

 

 

1 Reply
JeromeS
Partner
Partner

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