topic Re: Conditional Filter in a dataset in SAS Viya

Conditional Filter in a dataset

Devilsmile_20 — Fri, 27 Nov 2020 19:11:06 GMT

Hi,

I am in a middle of creating a Sales Dashboard where I have to create a Conditional Filter for my Bar & Line chart. My dataset consists 24 months data of actual against planned numbers. I want to see only current year plan and actual data with plus next 3 months planned number. Any suggestions on how to apply filter in such a way that I don't need to adjust it manually every month. Thanks in advance.

Re: Conditional Filter in a dataset

SASKiwi — Fri, 27 Nov 2020 22:42:17 GMT

One way to handle this is to derive a numeric month counter in your data. For example current month could be 0, back one month -1, back two months -2 and so on. Your planned data would be forward one month +1 and so on. Then your filter based on the month counter would be from -11 to +3 to get the last 12 months actual data plus the next 3 months planned data.

Re: Conditional Filter in a dataset

Devilsmile_20 — Sun, 29 Nov 2020 19:12:06 GMT

Hey,

Thanks for the solution but can you elaborate on how to derive the counter in the data.

Re: Conditional Filter in a dataset

BrunoMueller — Mon, 30 Nov 2020 17:37:21 GMT

I assume you want to do this in a Visual Analytics Report, is that right? If so, I would move to the Visual Analytics board.

Please have a look at this post https://communities.sas.com/t5/SAS-Communities-Library/Calculating-First-and-Last-Days-of-the-Current-and-Future-Months/ta-p/307993 by @Renato_sas. He explains on how to calculate the dates your are after.

To save you some time, here is the formula to calculate n months from "now"

DateFromMDY(( ( ( Month('now - date'n) + ( 'n_months'p - 1 ) + 1200 ) Mod 12 ) + 1 ), 1, ( ( Year('now - date'n) + Trunc(( (Month('now - date'n) + ( 'n_months'p - 1 ) + 1200 ) / 12 )) ) -100 ))

Please note, this expression is using "now - date" (datepart of now) and a parameter named n_months. I used the parameter for testing purposes, -n to go n months back, +n to go n months forward.

Some other examples, First of next month

DateFromMDY(( ( Month('now - date'n) Mod 12 ) + 1 ), 1, ( Year('now - date'n) + Trunc(( Month('now - date'n) / 12 )) ))

Last day of current month, makes use of previous result

TreatAs(_Date_, ( TreatAs(_Number_, 'first_of_next_month'n) - 1 ))

Re: Conditional Filter in a dataset

SASKiwi — Wed, 02 Dec 2020 21:49:20 GMT

It's actually very easy. Assuming you already have a reporting date defined as a SAS date in your data this is how you do it - Reporting_Month is the counter:

data want;
  set have;
  Reporting_Month = intck('MONTH', today(), Reporting_Date);
run;