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

Custom Aggregation by week does not work

Reply
Super Contributor
Posts: 268

Custom Aggregation by week does not work

Hi,

I am using SAS VA 7.1. I am trying to build a very simple crosstav report with GrossCost as the measure  and "week of" as the category. I am trying to calculate the previous weeks spend as a new measure. How do I do this? When I change the option to _inferred_ it does not work. However, if I have it as _month_, it seems to work but gives me a monthly figure. I see the below formula when I use by monthly.

RelativePeriod(_Sum_, 'GrossCost'n, 'weekOf'n, _ByMonth_, -1, _Full_, {Date})

Thanks,

saspert

SAS Super FREQ
Posts: 287

Re: Custom Aggregation by week does not work

Hello,

Unfortunately, we do not currently support a weekly interval for our periodic calculations.

I believe that the supported intervals are: Day, Month, Quarter, Year.

Thanks,

Sam

Super Contributor
Posts: 268

Re: Custom Aggregation by week does not work

Thanks Sam. Can you look into this option? I was trying to use a intnx function on 'week of' field -> get previous week and then do a self-join on the table to join on table A's current week to table B's prev week. So this can done in SAS EG. But I am having difficulty doing it in SAS VA 7.1

I saw this video which seems to be helpful. But I cannot find the intnx function when I go to Data Exploration->create a new query-> bring in my SAS dataset from sas lib-> add all fields-> click on + sign to add a new field -> click on expression to create an expression.

Get Started with SAS Visual Data Builder: Query Enhancements Tutorial | SAS Training

Regular Contributor
Posts: 196

Re: Custom Aggregation by week does not work

Hello Raj,

Can you share your 'Week of' column,

Teja

Super Contributor
Posts: 268

Re: Custom Aggregation by week does not work

Thanks venkata,

29MAR2015:00:00:00.000

05APR2015:00:00:00.000

12APR2015:00:00:00.000

19APR2015:00:00:00.000

26APR2015:00:00:00.000

03MAY2015:00:00:00.000

10MAY2015:00:00:00.000

17MAY2015:00:00:00.000

24MAY2015:00:00:00.000

I am getting out of a ms sql server db. have fun Smiley Happy

Super Contributor
Posts: 268

Re: Custom Aggregation by week does not work

I just raised a ticket with SAS Support. My boss can see all the functions but I cannot even though we both have the same access (VA admin) in SAS MC. This is in data exploration tab when I try to follow the steps -

I saw this video which seems to be helpful. But I cannot find the intnx function when I go to Data Exploration->create a new query-> bring in my SAS dataset from sas lib-> add all fields-> click on + sign to add a new field -> click on expression to create an expression.


Yayyy!! we know what could be the issue.

SAS Super FREQ
Posts: 287

Re: Custom Aggregation by week does not work

Thanks for the update Raj.

That is very strange that you can't see the functions, but I'm sure the Support folks can help.

Super Contributor
Posts: 268

Re: Custom Aggregation by week does not work

Does my name show up? All this while,  I thought my sas community handle does.

SAS Super FREQ
Posts: 287

Re: Custom Aggregation by week does not work

I believe your name used to show but it doesn't now. I will refrain from using your name if you would prefer.

Super Contributor
Posts: 268

Re: Custom Aggregation by week does not work

Not a big deal at all!! I guess maybe because you are an admin type, maybe my real name shows up.

Ask a Question
Discussion stats
  • 9 replies
  • 531 views
  • 0 likes
  • 3 in conversation