topic Re: How to create dynamic date filter in SAS VA 7.3 in SAS Visual Analytics

How to create dynamic date filter in SAS VA 7.3

anuragraishines — Mon, 27 May 2019 17:31:43 GMT

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

Re: How to create dynamic date filter in SAS VA 7.3

ASHISH2525 — Tue, 28 May 2019 04:39:45 GMT

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

Re: How to create dynamic date filter in SAS VA 7.3

PetriRoine — Wed, 29 May 2019 09:18:06 GMT

Hello @anuragraishines ,

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

Create parameter
Name: par_MaxDate_Text
Type: Character
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'))
```
Define your Drop-Down list roles:
Category = service_date_text
Parameter = par_MaxDate_Text
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.'))
```

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

Re: How to create dynamic date filter in SAS VA 7.3

anuragraishines — Tue, 28 May 2019 19:52:01 GMT

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

Re: How to create dynamic date filter in SAS VA 7.3

PetriRoine — Wed, 29 May 2019 09:20:47 GMT

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

Best regards,
Petri

Re: How to create dynamic date filter in SAS VA 7.3

anuragraishines — Wed, 29 May 2019 15:53:57 GMT

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

Re: How to create dynamic date filter in SAS VA 7.3

PetriRoine — Fri, 31 May 2019 09:53:23 GMT

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