Weekday to Weekday Comparison of Two different Years

[sat_lr]

Dear All,

My customer wants to compare sales of a particluar day from a specific week of that month to that of the last year. Say for ex. Friday from week 1 of Jan'14 vs Friday from week 1 of Jan'13 to see the % growth achieved. Say a Bar chart must specify Week 1 week 2 to show the user which week he/she is looking at for Friday with different years. Is there a way I can achieve this in VA.

greetings. satlr

[sat_lr]

any suggestions?

[jakarman]

Forget it as it too complicated to get good comparisons. Only when you would like to use a 28 year period you are having an equal year lay-out.
The weeknumber has different international definitions when you want that I would advice to go for ISO8601 definition. Every year there will be 1 or days shift (365/366 days a year). Your question is the same basic issue as the millennium bug question. Looks easy but is not.     
When you can transform your data to this question you could solve that.   

As SAS VA can use common SAS statements/language/functions when preparing the data I do not see problems using them.

SAS(R) 9.4 Formats and Informats: Reference the Weekv format is the one that is the international standard (iso8601).

[PeterWijers]

Hi Satir,

I did solve a likewise issue in the past, but I had to use a structured table that translates days into selectable keys. (non VA)

So while using this special table in the querry of VA, you create these options to be selectable in VA.

I wlll check if it might be possible in VA directly, assuming you alsways want to see the cumulated rusults of that week until the selected day.

I have holiday now, but will be back on this within 1-2 weeks.

Greetings  Peter

[sat_lr]

Hi Peter,

Greetings.

I must say with VA it is almost possible to achieve complex scenarios. In this particular case, I managed to produce visualizations that will allow user to compare fridays of that particular month to that of the last year. But fridays of all the weeks had appeared pertaining to that specific month of two different years. Although customer was happy seeing it as it was not possible with their existing reporting tool, I would be happy if I could manage to produce it in a little presentable way showing the weeks with numbers. FYI, there is an option already existing in VA which says 'weeknumbers'     but have to explore that a bit.

Let me know if you could leverage that option and come up with week numbers for your dates.

satlr

[sat_lr]

and to add to my previous reply, yes it is the cumulated result that weeks will have.

satlr

[PeterWijers]

Hi satlr,

Please consider the next option I often use for these problems.

I have created a date table that consist of all calendar dates from 1999 until two years ahead and added several calculated fields.  (with sas base)

Here a screen shot of the most common fields.

While using this table in the queries and join on the transaction date, I have a scale of fields directly available on any location.

If you now mathematically solve your problem creating a variable that is equal for the same situation.  (This data is also used outside of SAS VA and is located in a data warehouse)

For example I added a day of the month number that starts on the first Monday in the new financial period and combines the sat/sun into the Friday.

This way I have an equal day counter every year that gives me 20/20/25 days scheme.  (Fin.periods  defined as 4/4/5 weeks)

One of the profits I gained out of this, is that you only have to create the field once and can reuse it all the time without any additional effort.

(Anyway, if a definition would change, you only have to change it once in this table, and all of your graphics will follow automatically)

Maybe this is an idea to use.

Greetings and a lot of success solving your problem.

Peter

[sat_lr]

Hi Peter, appreciate your effort here. Thanks.

No doubt, It's a great way to solve this problem, in parallel I built a logical expression that gives me with the week number and weekday. So, here is how I solved it.

I filtered out the respective weeks and year that I needed for my analysis and visualization. Now I am able to compare it and also create YTD, differences, growth on top of it for the comparisons that I have derived.

satlr

[PeterWijers]

Hi satir,

Perfect, if you could solve it like that, its the most simple way and works fine if it covers your needs.  (also use it in some situations)

I expected a more complex definition/formula and mostly like to solve my problems in the data warehouse, so I also can use the results in other situations.

Greetings and good luck.

[partha_125]

Hi Peter,

Can you Pls post the code for the sample table you have created here.

thanks

[ballardw]

You might want to start a new thread.

Provide examples of your current data, your desired output, what code you have tried and since we are dealing with dates whether your dates are SAS date values or not.

[Wizard]

How about something like this:

     data _null_;
         curdte = Today();
       **  curdte = '21JAN2013'd;
         dow = weekday(curdte);
         incur= "week." || put(dow,1.);
    
         * now get same dow lastyr;
         lstyr = intnx('year',curdte,-1,'sameday');
         lstdow = intnx(incur,lstyr,1);

         format curdte date9.  lstyr date9.   lstdow date9.;

         call symput('dow',put(dow,1.));
         call symput('curdate',"'"||put(curdte,date9.)||"'d");
         call symput('lastyr', "'"||put(lstdow,date9.)||"'d");
         call symput('today_file',left(put(curdte,yymmddd10.)));
         call symput('start',left(put(curdte,WORDDATE.)));
     run;
     %put thisyr: &curdate    lastyr: &lastyr ;
     %put todayFile: &today_file     Start: &start;

[Ksharp]

Function nwkdom( ) .

data _null_;
/*Friday from week 1 of Jan'14*/
 d1=nwkdom(1,6,1,2014);
 d2=nwkdom(1,6,1,2013);
 put d1= date9. d2= date9.;
run;

Xia Keshan