Is there any way if i select a date and get the date from start of the month date to till selected date in Cross tab or list table object?
Selected Date: 23-Sep-2016 (Slider Object - Single Value) not Multiple
And get the Date from 01-Sep-2016 to 23-Sep-2016 with Revenue too....
Sunny Kumar Gupta
If i am not wrong you need the list of dates from first of the month to the selected date .
I have just tried writing a macro to achieve this.
PFB the macro:-
%put &first_of_month ;
do i="&first_of_month"d to &as_of_date ;
format list_date date9.;
After running the macro by passing the required date at the last line where we call a macro you get the "date_till_day" dataset with all the dates in it till now.
Hope this helps. If not kinldy let me know so that i can correct it.
It is possible that the RelativePeriod operator in an aggregated measure would be useful here, if you specify an offset of zero and you specify _ToDate_ as the scope.
I am not sure though whether the scope date can be specified via a report parameter, or if it must be hardcoded in your expression.
Let us know if that helps.
Sorry for the delay but I found the solution, what I did.
First, I took Slider for "select date" for single value
Then I took drop-down list object for the same date but converted in character format. You know very well that Drop-Down list object can't take Date/Datetime. So, for that reason, I converted the same date into character format and assign to drop-down list and create a parameter (Full Date_Char Parameter) to pass value.
And the right side panel, select "Required" option and create interaction between these two objects Slider and Drop-Down list.
Then I take a list table to display a revenue value.
Now, Select the same list table and go to right side panel "Filters" tab and click on "Add Filter" and create
Now what I'm doing here
Let an example:-
We're going to take the same date : 23-Sep-2016 (an example Numeric value of the date- 206)
Pass the drop down list value which is a parameter (Full Date_Char Parameter) in the Parse function convert the character to Date format and then TeastAs function convert Date to numeric format.
you'll get value - 206
DayofMonth function converts the date to Day and then subtract by 1.
you'll get value from this formula:- (23 -1) = 22.
Subtract these two values and get the start of the month date..
Value :- 184 (1-Sep-2016)
The betweeninclisive function gives the range.
184 to 206 (1-sep-2016 to 23-sep-2016)
Through this the way you can get the date from the start of the month date to till selected date.
Sunny Kumar Gupta
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.