Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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