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