turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Visual Analytics
- /
- Showing parts of a table and obtaining a running-y...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-16-2015 09:54 AM

Dear SAS community,

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

Month | June | May | April | March | February | January | ||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|

Category | value | YTD value | value | YTD value | value | YTD value | value | YTD value | value | YTD value | value | YTD value |

A | 1 | 14 | 3 | 11 | 3 | 8 | 2 | 5 | 3 | 3 | 0 | 0 |

B | 1 | 5 | 1 | 4 | 1 | 3 | 0 | 2 | 1 | 2 | 1 | 1 |

C | 1 | 9 | 3 | 8 | 1 | 5 | 2 | 4 | 2 | 2 | 0 | 0 |

D | 0 | 4 | 1 | 4 | 2 | 3 | 0 | 1 | 1 | 1 | 0 | 0 |

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

Month | April | |
---|---|---|

Category | value | YTD value |

A | 3 | 8 |

B | 1 | 3 |

C | 1 | 5 |

D | 2 | 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!