Custom Aggregation by week does not work

[saspert]

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

[Sam_SAS]

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

[saspert]

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

[TejaSurapaneni]

Hello Raj,

Can you share your 'Week of' column,

Teja

[saspert]

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

[saspert]

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.

[Sam_SAS]

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.

[saspert]

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

[Sam_SAS]

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

[saspert]

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