Bar Chart to show count between dates

[LauraMorris]

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.

[zwilson_borg]

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.