topic Re: Rolling Dates in SAS Visual Analytics

Rolling Dates

mjaitly — Tue, 14 Oct 2014 07:01:35 GMT

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.

Re: Rolling Dates

sat_lr — Tue, 14 Oct 2014 12:12:31 GMT

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

Re: Rolling Dates

PeterWijers — Wed, 15 Oct 2014 17:49:43 GMT

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.

Re: Rolling Dates

RDHamm — Tue, 28 Oct 2014 21:23:35 GMT

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)

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 )

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

Re: Rolling Dates

AnnaBrown — Wed, 29 Oct 2014 17:52:56 GMT

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

Re: Rolling Dates

mjaitly — Thu, 30 Oct 2014 07:01:45 GMT

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

Re: Rolling Dates

mjaitly — Thu, 30 Oct 2014 07:17:33 GMT

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.

Re: Rolling Dates

PeterWijers — Thu, 30 Oct 2014 12:04:11 GMT

Hi mjaitly

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

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.

Succes, Peter

Re: Rolling Dates

mjaitly — Fri, 21 Nov 2014 05:39:18 GMT

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

Re: Rolling Dates

PeterWijers — Fri, 21 Nov 2014 06:10:02 GMT

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)

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.

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

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

Re: Rolling Dates

mjaitly — Fri, 21 Nov 2014 06:15:54 GMT

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

Re: Rolling Dates

PeterWijers — Fri, 21 Nov 2014 07:30:41 GMT

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