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

Showing parts of a table and obtaining a running-year statistic

Reply
New Contributor
Posts: 4

Showing parts of a table and obtaining a running-year statistic


Dear SAS community,

I have two questions regarding SAS VA 7.1. First of all, let's assume I have the following crosstab:

MonthJuneMayAprilMarchFebruaryJanuary
Category valueYTD value valueYTD value valueYTD value valueYTD value valueYTD value valueYTD value
A11431138253300
B151413021211
C193815242200
D041423011100

Imagine I would only like to display this crosstab for the month April, i.e. I want to display just the following:

MonthApril
Category valueYTD value
A38
B13
C15
D2

3

I obviously cannot use a filter to achieve this view as the dynamically computed YTD (year-to-date) value depends on the values from the months before: it will yield the wrong value!

Q1: Is there any way to achieve displaying only a part of a table?

My second question is about obtaining a running-year statistic, i.e. the cumulative sum of some measure of the preceding (full) year. Given a measure 'measure' and a datetime item 'date', I now do this by creating an aggregated measure containing the following:

CumulativePeriod(_Sum_, 'measure'n, 'date'n, _Inferred_, _ByYear_, 0, _Full_, {Date}) + RelativePeriod(_Sum_, 'measure'n, 'date'n, _ByYear_, -1, _Full_, {Date}) - CumulativePeriod(_Sum_, 'measure'n, 'date'n, _Inferred_, _ByYear_, -1, _Full_, {Date})

This works just fine for a sum statistic but for other statistics it fails as it is an addition of three independent aggregations: this solution won't do for a distinct count, for instance.

Q2: is there any full solution for gathering a running-year statistic?

Thanks in advance for sharing your ideas!

Post a Question
Discussion Stats
  • 0 replies
  • 174 views
  • 0 likes
  • 1 in conversation