Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
I have a table of cases showing the start and end date of their activity (one record per case). I want to be able to display a line chart showing how many are active at any given date, this would be the cumulative number of cases based on start date minus the cumulative number of cases based on end date presented against date (with y axis being able to show by year, by month and by week of year). How do I do that?
Cheers
PC
Would you be able to share sample with expected output?
Hi Kush
I'll try to describe an example.
Lets say we have four people making up the table:
Case Start_Date End_Date
1 12-Dec-2019 2-Jan-2020
2 1-Jan-2020 10-Jan-2020
3 3-Feb-2020 15-Mar-2020
4 1-Jan-2020 1-Mar-2020
Then I want to make a line chart that shows Month on the X axis and total active cases on Y axis, where we would have:
Month Active Cases
Jan 3
Feb 2
Mar 2
…. 0
Dec 1
I want to be able to change the X-axis to be e.g. Year, so:
2019 1
2020 3
Cheers
PC
You may need to generate all the Dates between your start and end dates using below logic.
Data:
Load *,Start_Date+IterNo() as Date
Inline [
Case, Start_Date , End_Date
1, 12-Dec-2019, 2-Jan-2020
2, 1-Jan-2020, 10-Jan-2020
3, 3-Feb-2020, 15-Mar-2020
4, 1-Jan-2020, 1-Mar-2020 ]
While Start_Date+IterNo()<=End_Date;
Once you generated Date field using above logic , you can create the master calendar using dates which will contain the Year, Month etc.
Then finally you can create the chart using Dimension Month or Year and measure count(distinct Case)