topic Re: Cumulative sum in QlikView

Cumulative sum

Mon, 29 Dec 2014 11:33:50 GMT

Hello Friends,

I want to show the cumulative sum of headcount(YTD Headacount) month on month increase and decrease in the headcount .

Attached is the sample qvw application for the same.

Scenario:

If i select year as 2014 then in the Chart Absolute Headcount for the month of Nov and Dec values should be reflected as 14 instead of 5 because there is no hire and termination in the month of Nov and Dec and in the Month of Oct the headcount was 14 so it should be reflected as YTD headcount.

Please suggest how to get the above output.

Re: Cumulative sum

anbu1984 — Mon, 29 Dec 2014 12:06:14 GMT

Your headcount should be 13(16-3). Correct?

=Count({<DateType={'Hire'}, Year={'<=$(=Year)'}>}EmpID) - Count({<DateType={'Term'}, Year={'<=$(=Year)'}>}EmpID)

Re: Cumulative sum

Mon, 29 Dec 2014 12:23:25 GMT

Hello Anbu,

Yes you are right ,at the end i should have 13 but When i am selecting year as 2014 then

Jan headcount should be headcount as of 31/12 /2013 + Net joiner /leavers of Jan 2014,

Feb headcount should be jan 2014 result + Net joiner /leavers of Feb 2014,

Mar headcount should be Feb2014 + Net joiner /leavers of Mar 2014 and so on.

which i am not able to get from the above expression.

Re: Cumulative sum

anbu1984 — Mon, 29 Dec 2014 12:31:35 GMT

Above expr gives 13. Check this app

Re: Cumulative sum

Mon, 29 Dec 2014 12:41:43 GMT

I am getting 13 in the chart Absolute Headcount in the Month of Oct.
I want to show the same 13 value in the month of Nov and Dec also,

as there is no hires and terminations in the month of Nov and Dec of 2014.

So the headcount at the end of Dec 2014 will be 13.

By using the above expression i am only getting the Net result.

I want to show the month on month Headcount Movement.

Re: Cumulative sum

anbu1984 — Mon, 29 Dec 2014 12:52:37 GMT

If(sum(Aggr(vOpHeadCountSelYear, Month)),sum(Aggr(vOpHeadCountSelYear, Month)),Above(Column(1)))

Re: Cumulative sum

Tue, 30 Dec 2014 05:10:38 GMT

I want to show the growth ,month on month

Expected Output for Year 2014:

Month	HeadCount
Jan	6
Feb	7
Mar	8
Apr	8
May	8
Jun	9
Jul	11
Aug	11
Sep	12
Oct	13
Nov	13
Dec	13

In your expression i am getting 13 for all the months.

Re: Cumulative sum

anbu1984 — Tue, 30 Dec 2014 07:16:58 GMT

Check this

=Count(TOTAL {<DateType={'Hire'}, Year={'<$(=Year)'}>}EmpID) - Count(TOTAL {<DateType={'Term'}, Year={'<$(=Year)'}>}EmpID)

+ RangeSum(Above(Count({<DateType={'Hire'}>}EmpID),0,RowNo())) - RangeSum(Above(Count({<DateType={'Term'}>}EmpID),0,RowNo()))