topic Re: How to calculate YTD that includes future data in SAS Visual Analytics

How to calculate YTD that includes future data

tweehh — Mon, 14 Mar 2022 20:25:58 GMT

Hi VA experts,

I'm working on a report to compare the YTD revenue of this year to the previous year. I was able to calculate the YTD revenue from the beginning of the year to the current date. However, there is some future revenue that was captured in the data therefore I want to include revenue from the beginning of the year to end of the current month. I could manually select the last day of the month for this year and last year. I was wondering there's a better way to do that without any manual effort.YTD (beginning of the year - end of current month)

Thank you for any help you can provide!

Re: How to calculate YTD that includes future data

SASKiwi — Tue, 15 Mar 2022 00:28:39 GMT

One way to do this is to add month and year counters to your VA load table. Set it to 0 for the current month / year, -1 last month / year, -2 for the month / year before that and so on. This would be automatically created each time you update your VA load table.

Then a simple static filter to get current year would be: where year_counter = 0 and month_counter <= 0, and for last year: where year_counter = -1

Re: How to calculate YTD that includes future data

tweehh — Tue, 15 Mar 2022 23:47:51 GMT

Thank you for your response! Can you provide more details on how to implement it in VA? Thanks!

Re: How to calculate YTD that includes future data

acordes — Fri, 18 Mar 2022 12:42:50 GMT

I've made the best experiences with a report_date variable in the data prep step via code.

You can use it easily in filters then.

I'm not sure if this screenshot helps you out, but it shows well how the parallel period reacts to filters.

good luck

Re: How to calculate YTD that includes future data

SASKiwi — Mon, 21 Mar 2022 02:08:38 GMT

Here's how you can calculate a month and year counter based on a reporting date:

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

I'm assuming here that you can do this in "normal" SAS before you load into VA.