Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
PC
Contributor II
Contributor II

Display cumulative numbers but limited by end dates for each record

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 

3 Replies
Kushal_Chawda

Would you be able to share sample with expected output?

PC
Contributor II
Contributor II
Author

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)