How do I filter based on the previous 30 days

[Niamh]

Hi,

I am currently using SAS® Visual Analytics Hub (V 7.4).

The dataset I have has multiple rows (representing insurance claims) for a given date. I am looking to show the number of claims in the last 30 days, going back from todays date.

I have previously tried the instructions for dynamic filtering outlined by skillman listed here:

https://communities.sas.com/t5/SAS-Visual-Analytics/Dynamic-Date-Filters/td-p/149208

However this is not working for me, as I have multiple rows (representing insurance claims) for a given date, when I treat the date as numeric is sums all the date numbers for this date. The attachment shows three columns the first is a list of dates in chronological order (representing the dates claims are opened), the second is the number of claims opened on that day and the 3rd shows the date as a number.

Can anybody recommend an alternative way of filtering based on the last 30 days? Or advise on how to manipulate the instructions in the aforementioned post to suit  the task at hand.

Niamh,

[ewv]

I'm a little confused by the description. It looks like that 3rd column shows the date as a number multiplied by the number of claims on that date.

If you just filter the first column using the method recommended by Skillman, and then sum the second column, won't that give you what you are looking for?

[Niamh]

Thank you for your response.

That is what I am looking for however the filter does not seem to work. When I use the filter described in the post the table is empty i.e. nothing is included in the filter. I applied the filter to the entire table and swapped  'VA_Date'n for the date variable in the first column. Also that last column is not needed, I have added it to show what the result of the 'treatas' function is:

TreatAs(_Number_, 'UR_Open_Day'n)

The aggregation of the variable is set as sum, I am unsure as to why it is using a multiplication as you say.

Kind Regards,

Niamh

[ewv]

Can you try changing TreatAs(_Number_, 'UR_Open_Day'n) to TreatAs(_Number_, UR_Open_Day) and seeing if that helps?

[SASKiwi]

If you are updating your data daily, then another way to do this would be to add a column to your data before loading which is a day counter based on today's date. So the day counter = 0 for today's date, = 1 for yesterday, = 2 for the day before yesterday and so on.

Then in VA  just create a simple filter: day counter <= 30.

To create the day counter in your data:

Day_Counter = today() - UR_Open_Day;