topic Re: Cummulative Sum in Visualization and Usability

Cummulative Sum

abhijith28 — Mon, 24 Dec 2018 12:36:15 GMT

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

Re: Cummulative Sum

sunny_talwar — Mon, 24 Dec 2018 13:01:39 GMT

Are you looking to do this in the script?

Re: Cummulative Sum

Gysbert_Wassenaar — Mon, 24 Dec 2018 15:16:15 GMT

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
];

Re: Cummulative Sum

thannila — Wed, 26 Dec 2018 05:31:25 GMT

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;

Re: Cummulative Sum

abhijith28 — Wed, 26 Dec 2018 07:56:52 GMT

Is it possible to implement it in front end?

Re: Cummulative Sum

sunny_talwar — Wed, 26 Dec 2018 11:56:33 GMT

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

Re: Cummulative Sum

pk — Mon, 08 Apr 2019 11:36:11 GMT

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

sunny_talwar — Mon, 08 Apr 2019 11:41:03 GMT

May be share a sample to show your issue

Re: Cummulative Sum

pk — Wed, 10 Apr 2019 10:34:09 GMT

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

Re: Cummulative Sum

pk — Wed, 10 Apr 2019 11:21:17 GMT

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

Re: Cummulative Sum

sunny_talwar — Wed, 10 Apr 2019 11:24:55 GMT

Have you tried this?

RangeSum(Before(Sum(Number), 0, ColumnNo()))

Re: Cummulative Sum

pk — Wed, 10 Apr 2019 12:15:00 GMT

yes...just tried ... not working in this/my scenario

Re: Cummulative Sum

sunny_talwar — Wed, 10 Apr 2019 12:23:06 GMT

What does not working means? Does it show an error message? Does it give you an incorrect output? Did it shut down your computer? Did it kill your electricity?

Not working can mean many things and not knowing what you are getting doesn't really help me help you.

Re: Cummulative Sum

pk — Wed, 10 Apr 2019 12:29:49 GMT

it is showing same amount...
not getting calculate cumulative sum of its

Re: Cummulative Sum

sunny_talwar — Wed, 10 Apr 2019 12:34:34 GMT

Would you be able to attach a sample so that I can see the issue?

Re: Cummulative Sum

pk — Thu, 11 Apr 2019 06:05:32 GMT

please check...
in the sample data,month no and CF_month are calendar fields, also expected result format is mentioned.

Re: Cummulative Sum

sunny_talwar — Thu, 11 Apr 2019 10:36:45 GMT

I was hoping for you to share a sample qvw where I can see the issue. By me building a dashboard on top of the Excel you have shared won't really help... because 1) I will be wasting a lot of my time to build a dashboard on dummy or aggregated data, 2) what might work with this dummy data might not work for your real scenario. I hope you understand my concerns.

Best,
Sunny

Re: Cummulative Sum

pk — Fri, 12 Apr 2019 09:41:45 GMT

find files and mentioned output format.

Re: Cummulative Sum

sunny_talwar — Fri, 12 Apr 2019 11:21:09 GMT

I used this expression

=RangeSum(Before(Sum([BS Balances]), 0, ColumnNo()))

to get this

Re: Cummulative Sum

pk — Sun, 14 Apr 2019 17:18:15 GMT

hello sunny_talwar,

thanks for reply and mentioned formula is working but i want add open bal into april months cumulative sum and then cumulative sum will get calculate for every months
so i want like -
open bal + GL Balances of April =cumulative sum for April month
(cumulative sum of April+ GL Balances of May) or (open bal + GL Balances of April + GL Balances of May ) = cumulative sum for May month

(cumulative sum of May+ GL Balances of june) or (open bal + GL Balances of April + GL Balances of May+GL Balances of june ) =cumulative sum for june month

hope you understand my req.