How to create dynamic date filter in SAS VA 7.3

[anuragraishines]

Hi All,

My data has 3 years of data(2017,2018 & 2019) and I have a field service_date in date format. I have to provide date selection drop down and if I select May-2019 than graph should show data from Jan-2017 till May-2019. If Jan-2018 is selected in drop down than data should be from Jan-17 till Jan-2018.

I know date parameter are not available in SAS VA 7.3.

Is there any workaround for this.

This is bit urgent quick response will be highly appreciated!!!

Regards,

Anurag Rai

[ASHISH2525]

Hi Anurag !

At first thought i suggest you can use Custom categories. you can create custom categories accordingly your need based on Date column.

Since it will take lot of manual efforts. you can also create flag for your dates & based on that flag you can create custom categories in VA. 

Flags should be created in ETL part so that you need not to do any manual stuff in VA.

Regards,

Ashish

[PetriRoine · Accepted solution]

Hello @anuragraishines ,

This is definitely doable. Here are the steps I did to achieve this:

1. Create parameter
   Name: par_MaxDate_Text
   Type: Character
   
   
2. Create list of dates for Drop-Down List
   The dates need to be in Character/String format - not Date format.
   The structure should be YYYY/MM (not MM/YYYY) because otherwise it will not sort correctly when applied to Drop-Down List.
   Name of the variable: service_date_text
   There are at least three ways to achieve this: 
   Firstly you can add a new variable to your source data if possible.
   Secondly you could create a new dummy table with single variable including all the dates needed in YYYY/MM character format.
   Thirdly you can utilize a new calculated column that creates the needed text variable from your service_date variable.
   Here is how I did it:
   
   
   
   

   Concatenate(Concatenate(Format(Year('service_date'n), 'BEST4.'), '/'), Format(Month('service_date'n), 'Z2.0'))

3. Define your Drop-Down list roles:
   Category = service_date_text
   Parameter = par_MaxDate_Text
   
   
4. Create a new date variable parameter_as_date from the parameter par_MaxDate_Text
   As this gets it's value from parameter each row will have the same value.
   Here is how I did it:
   
   

   DateFromMDY(Parse(Substring('par_MaxDate_Text'p, 6, 2), 'NUMX5.'), 1, Parse(Substring('par_MaxDate_Text'p, 1, 4), 'NUMX5.'))

   
   
   
5. Create a new filter for your chart:
   
   

   ( 'parameter_as_date'n Missing ) OR ( 'service_date'n <= 'parameter_as_date'n )

Here's some example pics from my work:

Let me know what you think?

Best regards,
Petri Roine

[anuragraishines]

hi PetriRoine,

Thanks for the prompt reply!!!

Till step 3 I can understand but in step you mentioned "Create a new date variable service_date_text from the parameter par_MaxDate_Text " What does from the parameter means here and in the screen shot you have created same field "service_date_text " with same format as Character but you have mentioned for date variable.

And what is "parameter_as_date" i believe in 4th step you are creating this field.

Regards,

Anurag

[PetriRoine]

You are absolutely correct. The step #4 wasn't right. I have now fixed that. Apologies for the trouble.

Best regards,
Petri

[anuragraishines]

Hi PetriRoine,

Thanks alot for the solution you saved my day.

I never thought in the lines of converting parameter values to use it in advance filter.

Earlier this kind of requirement I have completed by having the dates in numeric format unfortunately this time etl is not in my hand.

Does it have a performance impact while using your approach when dealing with close to 40 millions records???

Regards,

Anurag 

[PetriRoine]

Yes, it has an impact to performance.

This solution requires two calculated items: service_date_text and parameter_as_date. Each of these need processing.

Calculated items are not permanently added into data source tables - they are calculated on the fly when you open the report. This means every calculated item takes some processing time. 

So, if you can populate the Drop-Down list from a separate table having only single variable with distinct values, then you are going to save processing time. Firstly you save time by not having to process distinct date values from 40M rows of data: Secondly you save time by not having to create calculated item service_date_text when opening the report.

To see the performance effect I would simply use timer on my phone. Three timings opening the original report. Then three timings opening the report with this dynamic date filter.

I hope this helps!

Best regards,

Petri