topic Re: Cumulative Sum with the full accumulation option in QlikView

Cumulative Sum with the full accumulation option

Wed, 31 Dec 2014 08:59:52 GMT

Hello Friends,

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

Re: Cumulative Sum with the full accumulation option

Wed, 31 Dec 2014 12:24:53 GMT

Hi,

PFA updated application, I have added new chart "Absolute Head Count - New" which might suffice your requirement.

HTH

Regards,

Amay

Re: Cumulative Sum with the full accumulation option

chrismarlow — Wed, 31 Dec 2014 12:35:28 GMT

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;

DROP TABLE DateLink;

combined:
NoConcatenate
Load
*
Resident Hires;

left join (combined)
Load
*
resident Terms;

for i = 1 to NoOfRows('combined')

Let EmpID=peek('EmpID', $(i)-1, 'combined');

If IsNull(peek('TermDate', $(i)-1, 'combined')) then

//Load all dates after Hire
DateLink:
Load
Date,
$(EmpID) AS EmpID
Resident CommonCalendar
Where Date>=peek('HireDate', $(i)-1, 'combined');

ELSE

//Load dates between Hire and Term
DateLink:
Load
Date,
$(EmpID) AS EmpID
Resident CommonCalendar
Where Date>=peek('HireDate', $(i)-1, 'combined') AND Date<=peek('TermDate', $(i)-1, 'combined');

end if

next

Drop table 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.

Re: Cumulative Sum with the full accumulation option

udit_kumar_sana — Thu, 01 Jan 2015 16:16:25 GMT

Hi,

Please find the corrected answer in attachment.

Thanks & Regards,

Udit