topic Re: Cumulative Sums in SAS Visual Analytics

Cumulative Sums

Patrick — Mon, 18 Nov 2013 05:16:14 GMT

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?

Re: Cumulative Sums

MichelleHomes — Mon, 18 Nov 2013 09:44:11 GMT

Hi Patrick,

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

Kind Regards,

Michelle

Re: Cumulative Sums

SASBIROX — Wed, 20 Nov 2013 10:22:00 GMT

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.

Re: Cumulative Sums

Patrick — Wed, 20 Nov 2013 22:33:34 GMT

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?

Re: Cumulative Sums

SASBIROX — Thu, 21 Nov 2013 00:03:57 GMT

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. 😞

Re: Cumulative Sums

Patrick — Thu, 21 Nov 2013 05:15:38 GMT

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.

Re: Cumulative Sums

Bernard_SAS — Thu, 21 Nov 2013 13:44:29 GMT

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

Re: Cumulative Sums

Patrick — Thu, 21 Nov 2013 22:59:57 GMT

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