SAS Visual Analytics Report Control - Work Weeks Ago (WWAgo)

[utrocketeng]

Greetings Fellow Community Members,

To borrow from a popular radio show.. 'Long time reader, First time poster'.  While critiques are welcome, i will ask every to please be kind.  🙂

Use of Visual Analytics has grown leaps and bounds for my company over the past few years.  Among the many highlights of SASVA, users really value having data sets updated on a regular frequency to get the most recent understanding of business processes.  For the longest time, many report owners have used the notion of DaysAgo to default reports to only showing the current or previous day (extending to some number of days ago) without having to constantly update and save SAS Visual Analytics Reports.  Just set a control on the object to always filter 0, 1, 2, n days ago and every time the report opens that number of days will be displayed.  This is especially handy if you use the Report Email option to send PDF snapshots to users. 

Some internal co-workers have been after me for a long time to develop a similar solution for Work Weeks Ago (WWAgo).  NOTE:  I am aware that i could do this calculation at the time of ETL into LASR/CAS, however there are cases where I find this to not be the most effective path such as if the ETL is only processed a few times a week.  Anyhow, this capability would allow them to default various objects within a report to always show current and/or previous (or any number of work weeks) workweek to understand business challenges and use that data to drive resources where they can provide the greatest benefit.  After some morning contemplation time and loads of coffee, inspiration hit me rather hard this morning as to how to effectively do a calculated item in VA to achieve this WWAgo value. 

First two quick comments:

1. My company defines WW’s as (this makes using the on-board Visual Analytics WW function not entirely helpful)
   1. First day of week:  Monday
   2. First week of year:  First 4 day week
2. Quick Reminder:  SAS Stores Date and DateTime differently.
   1. Date is the integer number of days since January 1st, 1960 (EG Oct 30 2019 = 21852)
   2. DateTime is the integer number of seconds since January 1st, 1960 (EG Oct 30 2019 = 1888012800)

With item #2 above in mind, you will need to know how you data is stored in SAS and then choose a solution path below to add a new calculated item below.  Note the Date/DateTime column is denoted with a gold star and you can copy and paste the text version into a New Calculated Item dialog box and simply substitute your Date or DateTime field.

1. If your Date is a Date data type: 
   •  

   • Text Version:  

   • Floor(( ( TreatAs(_Number_, DatePart(Now())) - TreatAs(_Number_, DateFromMDY(1, 1, 2018)) ) / 7 )) - Floor(( ( TreatAs(_Number_, 'Date'n) - TreatAs(_Number_, DateFromMDY(1, 1, 2018)) ) / 7 ))

2. If your Date is a DateTime data type:
   •  
   •  Text Version:  

   • Floor(( ( TreatAs(_Number_, DatePart(Now())) - TreatAs(_Number_, DateFromMDY(1, 1, 2018)) ) / 7 )) - Floor(( ( TreatAs(_Number_, DatePart('Date'n)) - TreatAs(_Number_, DateFromMDY(1, 1, 2018)) ) / 7 ))

In the calculation above, I choose Jan 1st 2018 as a base date as it was a Monday.  I suspect the rest of the logic ought to make sense after that.

Using this in a report will allow you to set a filter such as this:

To default to the current week like this (note that the earliest date here is Monday October 28th):

Thank you for reading.  I hope this is sufficient information for you to replicate this technique against your data sets and that it inspires you to modify or create new value streams for your data / analytics within your business.  

Thanks,

Thomas

PS, i would like feedback so leave comments below.  

[FredrikE]

Hi!

Nice solution to a common problem 🙂

I normally have added the time dimension (year, quarter,mopnth,week) before loading data to VA and then use the rank function to get the. n last periods, but this is useful if you only have the date. 

I like it a lot!

//Fredrik