BookmarkSubscribeRSS Feed
qiaojinggg
Fluorite | Level 6

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 🙂

 

2 REPLIES 2
RichardPaterson
Obsidian | Level 7

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

qiaojinggg
Fluorite | Level 6

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. 🙂

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 1113 views
  • 0 likes
  • 2 in conversation