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

How to get the count of previous period in Data Preparation

Reply
Occasional Contributor
Posts: 8

How to get the count of previous period in Data Preparation

Hi, 

 

I'm trying to get the count of previous period (eg year) in data preparation which will gives me a new column of count.

An example of the output will be:

 

sas forum.png

 

I'm really unsure of how to create the prev year column though. I managed to get this output through using Aggregated Measure > Relative Period function in Report Designer, however due to other requirements this method is not feasible for me. 

 

Hence, I need to somehow create a new column in data query to get the count of previous periods.

 

I tried to create a sub-query with a newly created column called Prev_year, which outputs me the correponding previous year (eg 2011 > 2010) and added the NUM_CNT inside the sub-query, however this didnt do the trick.

INTNX('year',  BP13_CANCEL_APPLICATION.DTE_CANCEL, -1, 'b')

sas forum2.png

 

Any idea how I can go about doing this? Any help will be greatly appreciated. Thank you Smiley Happy

 

Contributor
Posts: 26

Re: How to get the count of previous period in Data Preparation

[ Edited ]

Hi, 

 

Is there a specific reason why you want to do this in the data builder? A simple aggregated measure will give you the same result. 

 

Richard

Occasional Contributor
Posts: 8

Re: How to get the count of previous period in Data Preparation

May i know how to do it in report designer? Using "RelativePeriod"?

 

I need to use the count of prev period to calculate percentage change. However, if i were to create it in report designer, it will be affected by the filter condition of the crosstab.

 perc diff empty.png

for my case, i only wish to display last 5 years of data on the crosstab (2011-2016). However, as you can see in the picture, the percentage difference for the "first year" (2011) is missing because data for 2010 is not displayed in the crosstab hence there's no reference to get the previous period count. 

 

I'm thinking if there's any ways to display the percentage difference for 2011 without displaying 2010 data. Hence, i wanted to explore if it's possible in data query. 

 

I'm open to any ideas or suggestions. Smiley Happy

Post a Question
Discussion Stats
  • 2 replies
  • 197 views
  • 0 likes
  • 2 in conversation