Qlik Community

Third-Party Qlik Jobs

Area for people to learn more about current Qlik related job postings.

Highlighted
abhijith28
New Contributor III

Cummulative Sum

Can anyone help me out with this

 

ABC:

Load * inline[

Month, Number
Jan, 1
Feb, 2
Mar, 3
Apr, 4
May, 5
Jun, 6
Jul, 7
Aug, 8
Sep, 9
Oct, 10
Nov, 11
Dec, 12

];

 

The output should be:

Jan 1

Feb 3

Mar 6

Apr 4

May 9

Jun 15

Jul  7

Aug 15

Sep 24

Oct 10

Nov 21

Dec 33

 

For every quarter it has to cumulative add, then it has to restart for each quarter. ex:- For Apr -- 4

 

 

 

 

 

20 Replies

Re: Cummulative Sum

Are you looking to do this in the script?
MVP & Luminary
MVP & Luminary

Re: Cummulative Sum

Perhaps something like this:

ABC:
Load 
*,
If(Match(Month,'Jan','Apr','Jul','Oct'),
Number,
Number+peek(CumulativeNumber)) as CumulativeNumber
inline[ Month, Number Jan, 1 Feb, 2 Mar, 3 Apr, 4 May, 5 Jun, 6 Jul, 7 Aug, 8 Sep, 9 Oct, 10 Nov, 11 Dec, 12 ];

talk is cheap, supply exceeds demand
thannila
Contributor

Re: Cummulative Sum

Data:
Load * inline
[
Month, Number
Jan, 1
Feb, 2
Mar, 3
Apr, 4
May, 5
Jun, 6
Jul, 7
Aug, 8
Sep, 9
Oct, 10
Nov, 11
Dec, 12
];
Data1:
load Month,
if(match(Month,'Jan','Feb','Mar'),'Q1',
if(match(Month,'Apr','May','Jun'),'Q2',
if(match(Month,'Jul','Aug','Sep'),'Q3',
if(match(Month,'Oct','Nov','Dec'),'Q4'
)))) as quarter,Number
Resident Data;
drop table Data;

Data2:
load *,
if(quarter=peek(quarter),rangesum(Number+peek(**bleep**)),Number) as **bleep**
resident Data1;
drop table Data1;

abhijith28
New Contributor III

Re: Cummulative Sum

Is it possible to implement it in front end?

Re: Cummulative Sum

Assuming you have a quarter field in your app... you can try this

Aggr(RangeSum(Above(Sum(Number), 0, RowNo())), Quarter, Month)

For the sample you provided... I created the Quarter field like this

ABC:
LOAD Month(Date#(Month, 'MMM')) as Month,
	 Number,
	 'Q' & Ceil(Month(Date#(Month, 'MMM'))/3) as Quarter;
LOAD * INLINE [
    Month, Number
    Jan, 1
    Feb, 2
    Mar, 3
    Apr, 4
    May, 5
    Jun, 6
    Jul, 7
    Aug, 8
    Sep, 9
    Oct, 10
    Nov, 11
    Dec, 12
];

This is what I got when I use the above expression with Month as the dimension

image.png

pk
New Contributor

Re: Cummulative Sum

hello,I having same scenario....want to calculate cumulative sum month for month
am using formula like
Aggr(RangeSum(Above(Sum(Number), 0, RowNo())), Month)
but it is not working in my case

Re: Cummulative Sum

May be share a sample to show your issue

pk
New Contributor

Re: Cummulative Sum

Hello ..
need to calculate cumulative sum in pivot table only 
please check sample data and also mentioned expected result format.

pk
New Contributor

Re: Cummulative Sum

Hello ..
need to calculate cumulative sum in pivot table only 
please check sample data and also mentioned expected result format.