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?
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
Hi Patrick,
I understand this feature to be available within the next release VA 6.3 (due mid December 2013).
Kind Regards,
Michelle
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.
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?
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. 😞
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.
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
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.