Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative Sum with the full accumulation option

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

3 Replies
Not applicable
Author

Hi,

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

HTH

Regards,

Amay

chrismarlow
Specialist
Specialist

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.

udit_kumar_sana
Creator II
Creator II

Hi,

Please find the corrected answer in attachment.

Thanks & Regards,

Udit