BookmarkSubscribeRSS Feed
makarand
Obsidian | Level 7

Hi,

my requirement is I want to filter the period  (say 01-31 May 2016 / using slider maybe)

and see the sales for this year as well as last year for the same period.

the period could be dates/months/years but I want to see this year value as well as last year value.

I tried parellel period and relative period but both disappears when i put period less than a year.

illustative diagram is attached.

hope this is possible wo changing dataset.

Scenario >

01may2016 to 31 May 2016 >> 01-31 may 2016 and 2015

01jan2015 to 31dec2015 >> 010115 to 31122015 and 01012014 to 31122014

 

 


SampleChart.png
7 REPLIES 7
BrunoMueller
SAS Super FREQ

Hi

 

You need to create a filter on your report object, the filter can use any parameter value. Please note a parameter can only be numeric or character.

 

So one approach could be:

Create a new calculated date item like below. This will create a character item in form of 2016M05.

Format('date'n[Raw], 'YYMM7.')

Then put a dropdown list into the canvas area and assign this the calculated item from before to this dropdown list.

 

Create a parameter of type character and assign it to the dropdown list.

 

then create a filter on you bar chart similar to this:

( Year('date'n) = ( Parse(Substring('dateYYMM_p'p, 1, 4), 'F12.') - 1 ) )
OR
( Year('date'n) = Parse(Substring('dateYYMM_p'p, 1, 4), 'F12.') )

Now whenever to select a given month from the dropdown list, the bar chart will be filtered accordingly.

 

The example only covers filter for the selected year and year - 1, it needs to be adapted to also check for the month.

 

You then have data from two years and can use Periodic operators

 

Bruno

makarand
Obsidian | Level 7
Thanks Bruno..
Will check as I want to give range for minimum 10 days diff and max year..
makarand
Obsidian | Level 7
Hi Bruno,
Above method u suggested create only one bar for given month and not two bars for different year seperately as needed. +
I want to select both starttime and endtime which is not possible here.
Plz suggest alternatives as I'm not proficient with t using parameters
BrunoMueller
SAS Super FREQ

Hi

 

If you want to select a date range, then I suggest to use a slider in the section prompt area. Assign the right date variable to the slider, then your report objects are filtered automatically

 

See a screenshot below:

Slider for date range

 

Bruno

makarand
Obsidian | Level 7

i have created a filter like attached file  or text

( ( Year(DatePart('INVOICE_DATE'n)) > ( Parse(Substring('start_date'p, 5, 4), 'F12.') - 1 ) ) AND ( Year(DatePart('INVOICE_DATE'n)) <= ( Parse(Substring('end_date'p, 5, 4), 'F12.') - 1 ) ) ) OR ( ( Year(DatePart('INVOICE_DATE'n)) > Parse(Substring('start_date'p, 5, 4), 'F12.') ) AND ( Year(DatePart('INVOICE_DATE'n)) <= Parse(Substring('end_date'p, 5, 4), 'F12.') ) )

 but still I am not able to generate the mentioned report.

will appriciate if you correct the code for the ranges.

 

The Slider will only accept numeric parameters and not text parameters, hence not able to parameterize to the dates like u suggested it for text parameters

 

( ( Year(DatePart('INVOICE_DATE'n)) > ( Parse(Substring('start_date'p, 5, 4), 'F12.') - 1 ) ) AND ( Year(DatePart('INVOICE_DATE'n)) <= ( Parse(Substring('end_date'p, 5, 4), 'F12.') - 1 ) ) ) OR ( ( Year(DatePart('INVOICE_DATE'n)) > Parse(Substring('start_date'p, 5, 4), 'F12.') ) AND ( Year(DatePart('INVOICE_DATE'n)) <= Parse(Substring('end_date'p, 5, 4), 'F12.') ) )

 


SampleChart-Filter.png
BrunoMueller
SAS Super FREQ

Hi

 

I do not know what the content of the start_date parameter looks like, so difficult to say

 

Also is invoice_date really a datetime value? If it is just a date, you do not need the datepart operator?

 

to test the outcome of your expressions, I suggest to create a new data item and then use the preview button to see the outcome of an expression, see also below. This should help with debugging.

Capture.PNG

 

Bruno

makarand
Obsidian | Level 7

Thnks Bruno,

 

I created filter which will include date values for this year and last year as u suggested but with the Date9. format and then use datefromMDY to create required range/period.

Thnks again for your help.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 1970 views
  • 0 likes
  • 2 in conversation