Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Cumulative Sums

Accepted Solution Solved
Reply
Respected Advisor
Posts: 4,130
Accepted Solution

Cumulative Sums

I would like to show Year to Date cumulative sums for various time hierarchies (week, month, quarter, year). How would I do this using VA6.2 - eg. for showing YtD values per month and company?


Accepted Solutions
Solution
‎11-21-2013 08:44 AM
SAS Employee
Posts: 1

Re: Cumulative Sums

Hi Patrick,

You are correct, this is exactly addressed in the new Visual Analytics 6.3 scheduled to come out later in December (date can always shift - I don't want to commit a date as only SAS Product Management can).  I have been working on an early release for testing purposes, and one of the major new functionality is the ability to have YTD (or any period-to-date) calculations automatically performed based on a date field.  It will certainly makes those types of calculations very easy.   It also allows the user to define a relative period (eg. sales compared to "same-month-last-year") in your calculations.

As usual, when the release is production and official, a formal announcement will be made and you will be able to see that new feature.

I understand this does not address how to do it in current release (6.2)... The best approach on 6.2 is along the lines of what SASBIROX proposes.  I also have seen some people do pre-calculations on the data before it is loaded in memory.  Once again, all this will be made *much* easier on 6.3.

Bernard

View solution in original post


All Replies
Trusted Advisor
Posts: 1,287

Re: Cumulative Sums

Hi Patrick,

I understand this feature to be available within the next release VA 6.3 (due mid December 2013).

Kind Regards,

Michelle

Occasional Contributor
Posts: 18

Re: Cumulative Sums

This might work .... you can create a computed column with a formula something like this formula.

I'm assuming the values Transaction Date and Product Sale Price.

$YTD

IF (

      (

        Year (TransactionDate) = Year(DatePart( NOW())  )

        )

   AND (

             DayOfYear(TransactionDate) <= DayofYear(Datepart(NOW()) )

             )

  )

RETURN Product Sale Price

ELSE 0

Message was edited by: Patricia Aanderud Then you could Sum the $YTD value or create an aggregated calculation from it.

Respected Advisor
Posts: 4,130

Re: Cumulative Sums

Thanks Tricia

Question: If I'm having something like "quarter" in the hierarchy then wouldn't any formula only pick up the data in this quarter? Or do I get something wrong here and even with having quarter it still would be possible to "look back" and - let's say in the second quarter - also include the data from the first quarter for summing?

Occasional Contributor
Posts: 18

Re: Cumulative Sums

I was thinking since it was a calculated measure that the hierarchy would understand based on the date value. 

I'm guessing that is not what is working from your answer.  :-(

Respected Advisor
Posts: 4,130

Re: Cumulative Sums

Nope it isn't.

Let's hope that the next VA version has some magic built for such a requirement as Michel announced :-)

As for now the approach I will be taking is to pre-calculate the cumulative sums for one aggregation level (YtD for months) as part of the ETL and this is then the only level where it can be used within VA.

Solution
‎11-21-2013 08:44 AM
SAS Employee
Posts: 1

Re: Cumulative Sums

Hi Patrick,

You are correct, this is exactly addressed in the new Visual Analytics 6.3 scheduled to come out later in December (date can always shift - I don't want to commit a date as only SAS Product Management can).  I have been working on an early release for testing purposes, and one of the major new functionality is the ability to have YTD (or any period-to-date) calculations automatically performed based on a date field.  It will certainly makes those types of calculations very easy.   It also allows the user to define a relative period (eg. sales compared to "same-month-last-year") in your calculations.

As usual, when the release is production and official, a formal announcement will be made and you will be able to see that new feature.

I understand this does not address how to do it in current release (6.2)... The best approach on 6.2 is along the lines of what SASBIROX proposes.  I also have seen some people do pre-calculations on the data before it is loaded in memory.  Once again, all this will be made *much* easier on 6.3.

Bernard

Respected Advisor
Posts: 4,130

Re: Cumulative Sums

Hi Bernard

Thanks. Good to get your confirmation for this new functionality. I'm fine as long as I can say that it's something which can be delivered within a reasonable timeframe.

"The best approach on 6.2 is along the lines of what SASBIROX proposes.."

I'm kind of astonished that you believe this can work at all. I'll give it another go then as what I've tried so far didn't allow me to include data for a calculation over the boundaries of a category. I might have something misunderstood/done wrong here then.

Thanks again

Patrick

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 2632 views
  • 7 likes
  • 4 in conversation