Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rajinikanth
Contributor III
Contributor III

Accumulation in Script

Hi All, I have data as below

T1:

Load * inline

[

Monthyear, Empid

Jan-2016, a1

Jan-2016, b1

Jan-2016, c1

Feb-2016, a1

Feb-2016, b1

Feb-2016, c1

Mar-2016, a1

Mar-2016,b1

Mar-2016, d1

Mar-2016, e1

];

I'm trying to calculated the accumulated employee count Monthyear wise. I need accumulation in the script as below.

No of employees in jan is 3

No of employees in feb is 3  and accumulated count is 6.(jan + feb)

No of employees in mar is 4 and accumulated count is 10 (jan + feb + mar)

expected output

New field

jan 3

feb 6

mar 10

15 Replies
mdmukramali
Specialist III
Specialist III

Hi,

Try the below working script.

T1:

Load *,

Date#(Monthyear,'MMM-YYYY') as MONTHYEAR

inline

[

Monthyear, Empid

Jan-2016, a1

Jan-2016, b1

Jan-2016, c1

Feb-2016, a1

Feb-2016, b1

Feb-2016, c1

Mar-2016, a1

Mar-2016,b1

Mar-2016, d1

Mar-2016, e1

];

LOAD

MONTHYEAR,

RangeSum(Peek('CountEmp'),Count_Emp) as CountEmp;

LOAD MONTHYEAR,

Count(Empid) as Count_Emp

Resident T1

Group By MONTHYEAR

Order By MONTHYEAR Asc;

Anil_Babu_Samineni

Perhaps this?

LOAD Monthyear, RangeSum(Peek('CountEmp'),Count(Empid)) as CountEmp Resident T1 Group By Monthyear;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

Never tried to combine GroupBy with Peek, does it work?

Anil_Babu_Samineni

Yes, I think so.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

Cool, I am going to try it out. Thanks for showing this

sunny_talwar

It did work... thanks Anil!! It feels awesome to learn new things

alis2063
Creator III
Creator III

Could you please explain the below that how does it work?

RangeSum(Peek('CountEmp'),Count_Emp) as CountEmp;

sunny_talwar

It's just summing the value from row above to the value in the current row to get you accumulated value

rajinikanth
Contributor III
Contributor III
Author

Hi Anil, Thank you for the solution with your solution i got the output as below,

   

CountEmpMonthyearEmpid
301-01-2016a1
301-01-2016b1
301-01-2016c1
601-02-2016a1
601-02-2016b1
601-02-2016c1
1001-03-2016a1
1001-03-2016b1
1001-03-2016d1
1001-03-2016e1

The actual outcome should be as below

 

CountEmpMonthyearEmpid
101-01-2016a1
101-01-2016b1
101-01-2016c1
201-02-2016a1
201-02-2016b1
201-02-2016c1
301-03-2016a1
301-03-2016b1
201-03-2016c1
101-03-2016d1
101-03-2016e1

In March  a1=3 and b1=3 as its accumulation in feb and jan is 2 and 1

In March d1 and e1 are new so they will have accumulation as 1

In march c1  accumulation is 2

a1+b1+c1+d1+e1   = 3+3 +2+1+1 =10