BookmarkSubscribeRSS Feed
znhnm
Quartz | Level 8

Hi, 

 

I have a dataset where I created a table in Excel that looks like the first example below. That shows, for example that in 2019, number of records for Q1 was 25, and son on. I calculated the Total with a very basic formula. It is easy to replicate it in VA using crosstab. However, I am struggling it to turn it into the second example. My question is, how to calculate Total for each year and then use this number to calculate the percentages? The other tricky part is that the percentages are cumulative in the second example. 

 

Thank you!

znhnm_1-1678121854516.png

 

1 REPLY 1
Sam_SAS
SAS Employee

Hello,

 

I believe you could calculate this by using two of the periodic operators in VA:

 

CumulativePeriod will return the year-to-date value for each quarter if you specify ByQuarter as the inner interval and ByYear as the outer interval.

Period will return the value for the current year if you specify ByYear as the interval.

 

So, you could make an expression like this:

CumulativePeriod(_Sum_, 'Expenses'n, _IgnoreAllTimeFrameFilters_,
'Quarter'n, _ByQuarter_, _ByYear_, 0, 1, _Full_, {Date}) 
/
Period(_Sum_, 'Expenses'n, _IgnoreAllTimeFrameFilters_, 'Quarter'n, _ByYear_)

And get a result like this:

pctytd.png

 

This is not exactly the same thing as the data in your opening post. You need the quarter and year to be the same column for this to work. In addition, your report object can only have one date column it for the periodic functions to work. You could work around this somewhat by making an extra character column with the years, for example.

 

Let us know if that helps,

Sam

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
  • 1 reply
  • 556 views
  • 0 likes
  • 2 in conversation