BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Patrick
Opal | Level 21

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Bernard_SAS
SAS Employee

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

7 REPLIES 7
MichelleHomes
Meteorite | Level 14

Hi Patrick,

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

Kind Regards,

Michelle

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
SASBIROX
Calcite | Level 5

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.

Patrick
Opal | Level 21

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?

SASBIROX
Calcite | Level 5

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

Patrick
Opal | Level 21

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.

Bernard_SAS
SAS Employee

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

Patrick
Opal | Level 21

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

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.

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