BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mystic_magical
Calcite | Level 5
Hi all,
I'm trying to create filter on date in my dashboard which will be use to filter data by selected date and one month previous date with in a same filter. Example - If I'm selecting Apr2023 in my drop-down list then filtered data will be having dates like Mar2023 & Apr2023.
Filtered data will be consumed for dashboard.

Thanks in advance.
1 ACCEPTED SOLUTION

Accepted Solutions
Sam_SAS
SAS Employee

Hello,

 

This is one of those things that feels more difficult than it should be. It is slightly easier in newer versions of VA.

 

The basic idea is that you will create a parameter that will store the selected date, and then use that parameter in an advanced filter in the target object(s). You do not want to put the selector control in the report prompt area or the page prompt area. It needs to be in the body of a page so that does not automatically act as a filter. (This would filter out everything but the selected month value.)

 

Due to some limitations, the filter selector needs to be something other than a slider control when a date parameter is assigned.

 

In the latest versions of VA you can use a filter expression like this:

BetweenInclusive('Transaction Month'n, 'Parameter 1'p -31, 'Parameter 1'p)
In 8.3 you would need a more cumbersome one:
 
( 'Transaction Month'n BetweenInclusive(TreatAs(_Date_, ( TreatAs(_Number_,
'Parameter 1'p) - 31 )), 'Parameter 1'p) )

I don't have an 8.3 test environment at hand, but here it is in 8.5:

filter.png

 

To be honest, I am a little confused about why this works as well as it does-- you would expect that subtracting 31 days from MAR2000 (which is treated as 01MAR2000) would include January because February only has 28 or 29 days. But as far as I can tell it is always returning only the desired "selected month and the one before".

 

A further workaround to make the filter selector a report-level control would be to filter on a second data source that is not mapped to the main one. That way you can set the parameter value but not perform automatic filtering.

 

Let us know if that helps. I can walk you through the steps in greater detail if needed.

 

Sam

View solution in original post

3 REPLIES 3
Sam_SAS
SAS Employee

Hello,

 

This is one of those things that feels more difficult than it should be. It is slightly easier in newer versions of VA.

 

The basic idea is that you will create a parameter that will store the selected date, and then use that parameter in an advanced filter in the target object(s). You do not want to put the selector control in the report prompt area or the page prompt area. It needs to be in the body of a page so that does not automatically act as a filter. (This would filter out everything but the selected month value.)

 

Due to some limitations, the filter selector needs to be something other than a slider control when a date parameter is assigned.

 

In the latest versions of VA you can use a filter expression like this:

BetweenInclusive('Transaction Month'n, 'Parameter 1'p -31, 'Parameter 1'p)
In 8.3 you would need a more cumbersome one:
 
( 'Transaction Month'n BetweenInclusive(TreatAs(_Date_, ( TreatAs(_Number_,
'Parameter 1'p) - 31 )), 'Parameter 1'p) )

I don't have an 8.3 test environment at hand, but here it is in 8.5:

filter.png

 

To be honest, I am a little confused about why this works as well as it does-- you would expect that subtracting 31 days from MAR2000 (which is treated as 01MAR2000) would include January because February only has 28 or 29 days. But as far as I can tell it is always returning only the desired "selected month and the one before".

 

A further workaround to make the filter selector a report-level control would be to filter on a second data source that is not mapped to the main one. That way you can set the parameter value but not perform automatic filtering.

 

Let us know if that helps. I can walk you through the steps in greater detail if needed.

 

Sam

mystic_magical
Calcite | Level 5
Manny Thanks Sam!!!
By using combination of parameter & advanced filter option I achieved my desired result.
Sam_SAS
SAS Employee
Great!! Thanks for letting us know it worked for you.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 3 replies
  • 1074 views
  • 0 likes
  • 2 in conversation