I want to create a chart to display head count growth by month across the selected calendar year. The data points are as on end of month. I will always have an opening head count at the start of the year. In this example it is 5. While I am able to derive cumulative sum by “Count” expression and “Full Accumulation” option, I am unable to add the constant Opening Head Count across all values to reflect the actual head count over months. Let me know what I am missing in my expression.
I am sure someone of you must have handled this kind of requirement. Is there any alternate way to implement this requirement?
Attached is the qvw file with the expected output in xls file
Interested to see if there is a solution to this using functions, as on something similar I resorted to scripting. Applying to your example I did the equivalent of creating a new DateLink table with all dates per EmpID for which they were hired (and not terminated), so a script like this on the end of your current model;
combined: NoConcatenate Load * Resident Hires;
leftjoin (combined) Load * resident Terms;
fori = 1 toNoOfRows('combined')
LetEmpID=peek('EmpID', $(i)-1, 'combined');
IfIsNull(peek('TermDate', $(i)-1, 'combined')) then
//Load all dates after Hire DateLink: Load Date, $(EmpID)ASEmpID Resident CommonCalendar WhereDate>=peek('HireDate', $(i)-1, 'combined');
//Load dates between Hire and Term DateLink: Load Date, $(EmpID)ASEmpID Resident CommonCalendar WhereDate>=peek('HireDate', $(i)-1, 'combined') ANDDate<=peek('TermDate', $(i)-1, 'combined');
To get closer to what you want you would need to add some extra logic to your expression (either through an extra field on CommonCalendar and some set analysis, or some kind of date test) to limit it to return just the month end points, otherwise you get bars for every day.