Qlik Community

New to Qlik Sense

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

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
LauraMorris
Contributor II
Contributor II

Bar Chart to show count between dates

I have a data set:

ID

StartDate

EndDate

I want to create a bar chart with date on the x-axis and a count of active IDs on the y-axis but need to do it in the app not the loader.

 

I know I can do this by creating a new table in the data load editor by doing:

[ActiveDates]:
noconcatenate LOAD
ID,
date(StartDate+IterNo()-1) as ActiveDate
while date(StartDate+IterNo()-1)<=EndDate;
load
*
resident DataTable;

 

but there are approx 5 million IDs and each has something between 1 year and 5 years worth of dates to iterate over so this is not a  practical resolution.

Labels (3)
1 Reply
zwilson_borg
Contributor III
Contributor III

The main thing you'll need is a calendar for your entire date range that isn't connected to your data. So for example if your earliest start date is 1/1/2016 and your last date is 6/21/2020 - you'll need a daily calendar for that entire range. 

Once that is in place you can just use that Date as the dimension and the following expression as the measure =SUM( Aggr( IF(Date >= [StartDate] and Date <= [EndDate], 1,0),Date,ID)). This uses the AGGR function to aggregate on the calendar date field and ID with a conditional if statement where it does the validation if the ID was valid during the time period. If so it returns 1 and is summed for that day.

You will need to check your edge cases for missing start dates/end dates. If this can occur you can either fix it in the backend script or use OR/ISNull statements to manage these cases.