cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
saitcopuroglu
Level IV

Creating a Cumulative Summary Column for different rows

Hi,

As you will understand from the question I am a newbie to JSL scripting/formulating/automating...I have a revenue data set which contains a column called as "year" of 2013 2014 2015... I need a Cumulative Sum for each year but when I create a column of Cumulative Sum (by the help of Graph Builder) it adds all the days and year one to another and ends up with grand 3 year total Cum Sum, where I would like the Cum Sum start over for each year. I could solve it by splitting or subsetting but since every day I need to send a report of revenue comparison it is really annoying. And this data changes every hour so that would be great to update the existing jmp with the added data and then create the Cum Sum Column for each year... Could someone help please. Thank you!

14 REPLIES 14
saitcopuroglu
Level IV

Re: Creating a Cumulative Summary Column for different rows

If(Row() == 1 | Lag(:Year) != :Year, :NetRoomRevenueWithOutVat, Lag(:CumSumByYear) + :NetRoomRevenueWithOutVat)

Re: Creating a Cumulative Summary Column for different rows

The only thing I can think to check is the value for :Year in the first row with a value of 0.00. If it is missing or different from the previous, then the results would make sense. Otherwise, I'm not able to reproduce it, at least with the data you sent in earlier.

Thanks,

Michael

Michael Crotty
Sr Statistical Writer
JMP Development
saitcopuroglu
Level IV

Re: Creating a Cumulative Summary Column for different rows

May I send the whole data? I really would appreciate if you manage which saves me a lot of time.

saitcopuroglu
Level IV

Re: Creating a Cumulative Summary Column for different rows

Thank you Michael!

XanGregg
Staff

Re: Creating a Cumulative Summary Column for different rows

In JMP 11 there is a Transform menu in most platforms that has a Cumulative Sum item. It creates a column with a formula of the form:

If( OrMZ( Row() == 1, :sex != Lag( :sex ) ), 0, Lag() )

+ If( Is Missing( :height ), 0, :height )


It also relies on the grouping variables being sorted, but it ignores missing values.