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

Rolling Dates

Reply
Occasional Contributor
Posts: 19

Rolling Dates

Hi

I need some help with dates. I have put up the range slider date controls however I want it in a way such that whenever I open the report, it should automatically show me the data for the last 7days or for last 7 months (in monthly reports). Can anyone let me know the code for this? Also is there any other alternative for date controls other than the range slider (I feel it occupies too much space).

Thanks

Manish.

Frequent Contributor
Posts: 85

Re: Rolling Dates

In this case, I believe you would have to go for sas macros. Injecting certain macro codes would do the job for you. I have given you the link that might help you up.

http://support.sas.com/resources/papers/proceedings13/343-2013.pdf

greetings

Regular Contributor
Posts: 172

Re: Rolling Dates

Hi Manish,

I solved this problem using a new calculated field.

If you define the new calculated field result as 1 or 0 depending on the date versus today-7,

you can use this field as a filter for the range slider.

Just try the list option to be able to select out of the 7 selected days.

The list box often works fine when the number of options is rathen small.

greetings and good luck.

Occasional Contributor
Posts: 19

Re: Rolling Dates

Hi Peter

Thanks for your reply. Can you please let me know the code if possible? I am tied up real ad with several reports so am not really having the time to experiment.

Thanks

Manish

Regular Contributor
Posts: 172

Re: Rolling Dates

Hi mjaitly

Here a screenshot of the calculated_item that detects if the record is within the last three years.

Year_range.jpg

If you use this calc.item as a filter for the selction_bar as I did or with a range_slider as you want to do.

Year_rangef.jpg

Succes,    Peter

Occasional Contributor
Posts: 19

Re: Rolling Dates

Hi Peter

Belated thanks for sharing this however when I give the below formula it does not work or in other words dates do not roll..they freeze. My idea is to set dates rolling for the last 7 days in my daily report. However when I logged in today the data still shows from 14th Nov to 18th Nov. Genration_Date is a field in the LASR table and is the date of transaction. In my daily report I want to see by default (that is when I log in every morning) data only for the last 7 days but the below formula in a calculated item does not work.

IF ( DayOfMonth('Generation_Date'n) >= ( DayOfMonth(DatePart(Now())) - 7 ) )

RETURN 1

ELSE 0

Please let me know what is wrong with this.

Thanks

Manish

Regular Contributor
Posts: 172

Re: Rolling Dates

Hello Manish,

I did set-up the formula in this way and it seems to work today.  (Have to test it of cource in the comming days)

21-11-2014 07-03-51.jpg

Please notice that I do use DayOfYear and not DayOfMonth.

While testing I found the follwing results where FullFormula is the calculated item as above.

21-11-2014 07-04-10.jpg

Seems to work for today because I already used the filter for this table as below.

21-11-2014 07-04-24.jpg

Please try this one, and we will see the coming days if it works like it does for me with the year selection.

Greetings  Peter

Occasional Contributor
Posts: 19

Re: Rolling Dates

Peter

Can you put up the written code instead of the visual. I am a little confused about the betweeninclusive..where to put exactly?

Thanks

Manish

Regular Contributor
Posts: 172

Re: Rolling Dates

Manish,

here the code.

I used the Between code because in this data source there is also predicted info in the future.

IF ( DayOfYear(DatePart(Now())) - DayOfYear(Date) ) Between [inclusive] (0, 7)

RETURN 1

ELSE 0

Occasional Contributor
Posts: 6

Re: Rolling Dates

Hi.

I did something similar.  In my case I have 2 dates : A system date of the data (called 'latestbusinessdayend') and a hotel occupancy date. (called 'occupancyDate').

I wanted a way to set a report to only look at say, 7 - 14 days from today or the prior 7 - 14 days.  The formula below creates a series of numbers (from negative to positive based on how far the occupancy date is from the system date)

I was able to accomplish this using the TreatAs function that is in version 6.4 and higher:  Note:  I did have a version before 6.4 that did this but was way more complex....

TreatAs(_Number_, 'occupancyDate'n) - TreatAs(_Number_, 'latestBusinessDayEnd'n)

Daysfromsystemdate.jpg

To implement this, use a date selector of choice then use this as a filter on your dates.  For example, the filter below will set the dates to be selected from +1 to +7

( 'Days from LBDE TreateAS'n > 1 ) AND ( 'Days from LBDE TreateAS'n <= 7 )

TreatasFilter.jpg

In my opinion the date slider works best for selections as it is pretty easy for the user.   If you want to make it smaller, you can put a non-selectable text box next to it.

Good Luck,  Roger

Community Manager
Posts: 422

Re: Rolling Dates

Hi mjaitly,

Thanks for posting your question! Did you get an answer that was helpful or correct? If so, feel free to mark as appropriate so that other community members can reference what worked.

Best,

Anna

Occasional Contributor
Posts: 19

Re: Rolling Dates

No not yet. I will need the code in the calculated field. By default I need the last 7 days or months from Jan'14 however if the user wants he should be able to choose the dates from the range slider control.

Post a Question
Discussion Stats
  • 11 replies
  • 931 views
  • 1 like
  • 5 in conversation