Display cumulative numbers but limited by end dates for each record

[PC]

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 

[Kushal_Chawda]

Would you be able to share sample with expected output?

[PC]

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

[Kushal_Chawda]

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)