Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- SAS Viya
- /
- Visual Analytics
- /
- Re: Calculate a cumulative sum over a period longer than 1 year?

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-19-2015 05:07 AM
(10622 views)

Hello,

I want to create variables that calculate a cumulative sum (running sum) over a period longer than 1 year. Perhaps I'm doing something wrong, but using CumulativePeriod or the other options the Cumulative sum begins again from 0 as the year changes

Can anyone help me solve this.

Thanks in advance

Chris Keen

15 REPLIES 15

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

ChrisKeen,

My apologies--I accidentally marked your question "answered" by mistake. I'm currently having trouble undoing my mistake.

I believe it is possible for you to mark the question back to "unanswered." My posting this message will bump your question back up in the activity stream - hopefully someone will see and get you some help.

I'm sorry for the inconvenience. Thank you for being part of the community!

Communities Admin

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Chris,

I think you are trying to calculate cumulative sum for financial year, if yes, it is not possible to calculate in this way, you can do this using custom category, cumulative period is only for calendar year (i.e.., Jan - Dec).

Lets hope that this option (YTD) is available in VA 7.2.

You want to compare MOM?

Regards,

Teja Surapaneni.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hello Teja,

Thanks for your reply. I am trying to calculate for a periode, for example from 1-1-2014 to date (in february 2015 the periode to calculate is 1-1-2-14 to 1-2-2015, or in June 2015, the period should be 1-1-2014 - 1-6-2015).

Have you an example that shows me how I can manually calculate this.

Thanks again

Regrads

Chris keen

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

hi,

You Want Cumulative Sum or Overall Sum ?

Thanks,

Teja Surapaneni.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi

I want a cumulative (running) sum - using my example in my the discussion meassge, from march 2014 til march 2015 without breaking and starting again from jan. 2015

Thansk again

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hello jay and Chris,

There have been a few threads on this topic.

I asked a developer who supports the periodic operators about this, and he doesn't know of a workaround in the current release. This feature is definitely on our radar, so hopefully it can be implemented in a future release.

Thanks,

Sam

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Sam for letting us know

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

I have made a calculation for running sum over a period longer than 1 year like this:

1. **Data Set:** DATE (in MM/YYYY format); Value

2. + **Date2Year**=DateFromMDY(1, ( Month('DATE'n) + ( ( Year('DATE'n) - 2014 ) * 12 ) ), 2015)

3. + **Value2Year=**CumulativePeriod(_Sum_, 'Value'n, 'Date2Year'n, _Inferred_, _Inferred_, 0, _Full_, {Date})

4. + **No.=**'Date2Year'n. **Format display as Day of Month**. "No." is needed to replace "Date2Year" in the Table.

*Same technic I'm using for calculating: day to day difference, week to week difference, cumulative sum by days in week, etc.*

I hope it's clear and will help.

Robert R.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

I think you are limited to 365 values with this solution.

I have to do a cumulative with data on more than 1 year and one data per day.

I created date2year in SAS Base like this : date2year = '01jan2015:0:0:0'dt + datepart(date);

In Visual Analitics, i created value2year like : CumulativePeriod(_Sum_, 'Value', 'Date', _Inferred_, _Inferred_, 0, _Full_, {Date})

But value2year stays empty, I think this is due to the fact that date2year is in datetime format...

Have you any solution ?

Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

There is solution - but very, very slow for daily summing, better for month, good for quarter or year summing

**For e.g. data for 2014, 2015 years**

**"Quantity" - aggregated measure**

**"Date" - calendar data**

1. YearNo = Year('Date'n) *measure*

2. QtyYTD = CumulativePeriod(_Sum_, 'Qty'n, 'Date'n, _Inferred_, _ByYear_, 0, _Full_, {Date}) *aggregated*

3. QtyPrevYear = IF ( Avg [_ByGroup_] ('YearNo'n) = 2014 ) RETURN 0 ELSE ParallelPeriod(_Sum_, 'Qty'n, 'Date'n, _ByYear_, _ByYear_, -1, _Full_, {Date}) *aggregated*

4. Qty2YTD='QtyPrevYear'n + 'QtyYTD'n *aggregated - gives sum we are looking for*

Best Regards

Robert R.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I'm sorry how to obtain rolling sum on 4 week?

@RR28 wrote:Hi,

I have made a calculation for running sum over a period longer than 1 year like this:

1.

Data Set:DATE (in MM/YYYY format); Value2. +

Date2Year=DateFromMDY(1, ( Month('DATE'n) + ( ( Year('DATE'n) - 2014 ) * 12 ) ), 2015)3. +

Value2Year=CumulativePeriod(_Sum_, 'Value'n, 'Date2Year'n, _Inferred_, _Inferred_, 0, _Full_, {Date})4. +

No.='Date2Year'n.Format display as Day of Month. "No." is needed to replace "Date2Year" in the Table.

Same technic I'm using for calculating: day to day difference, week to week difference, cumulative sum by days in week, etc.

I hope it's clear and will help.

Robert R.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Can you please say in which version it is Possible?

**Don't miss out on SAS Innovate - Register now for the FREE Livestream!**

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.