topic Re: Get data from previous period based on from and to date in SAS Visual Analytics

Get data from previous period based on from and to date

Dominik4 — Mon, 22 Nov 2021 08:12:12 GMT

Hi,

I'm a newbie in VA and I would like to get a summed up value from a previous period (depending on user from and to filter).

Example:

User-Filter with from date 01.05.2021 and to date 31.10.2021

Column 1: Sum(MyNumericColumn) --> data between 01.05.2021 and 31.10.2021

Column 2: Sum(MyNumericColumn) --> data between 01.05.2020 and 31.10.2020

I already tried to create an calculated column with aggregated (periodic) element as resulttype aggregated measure without success. I get no results and besides that it looks like I can just refer to one single date and not a from to period.

Is something I descriped possible in VA?

Re: Get data from previous period based on from and to date

Sam_SAS — Mon, 22 Nov 2021 14:50:18 GMT

Hello,

If you just want to display those two values, there is no need to create a calculated item.

Here, the slider control is configured to filter the table through the Actions tab.

But I'm not sure if you want to do something more complex. If you want to display these filtered, aggregated values along with unfiltered values or detail (unaggregated) values in the same object, then calculated expressions would be needed.

Let us know if that helps,

Sam

Re: Get data from previous period based on from and to date

Dominik4 — Tue, 23 Nov 2021 05:51:34 GMT

Hello Sam,

thank you for you replay.

I need to aggregate filtered and unfiltered rows (in two different columns). I try to explain it with your example screenshot.

Assumed the user set the filter to 01.05.2021 to 31.10.2021 I would like to have the following columns:

Product Line, ProductSaleFilterYear, ProductSaleYearBefore

The column ProductSaleYearBefore should contain data for each Product Line from 01.05.2020 to 31.10.2020

As you say I guess I need a calculated expression but until now I was not successfull. Can you help me choosing the right expression and setting the correct parameters?

Re: Get data from previous period based on from and to date

Sam_SAS — Tue, 23 Nov 2021 15:10:43 GMT

Hello,

Thanks for the additional details.

I'm not sure the exact behavior you are asking for is possible. It would be difficult, at any rate.

Something relatively easy to do is to calculate last year's value and display it:

Here, the slider is still just a filter, but we show last year's value for each month.

You could switch the month to quarter if that would be closer:

In this example I am using the ParallelPeriod operator to get the previous year's values:

ParallelPeriod(_Sum_, 'Product Sale'n,
_IgnoreAllTimeFrameFilters_, 'Transaction Month'n, _Inferred_,
_Inferred_, -1, _Full_, {Date})

If I change the slider to select a single value, and I move the Quarter column to the Hidden role in the table, I get this, which might be a little closer yet to what you are asking:

Possibly someone else will have a suggestion to match your requirement more closely.

Sam

Re: Get data from previous period based on from and to date

Dominik4 — Wed, 24 Nov 2021 09:06:07 GMT

Hello Sam,

thank you for that suggestion. I will test it to learn and I talk to my collueges to ask if this helps. Problem is that we have defined seasons (for example 01.05.2021 and 31.10.2021 ) so quarters are not close enough.

In worst case I could create another data source and using VA just for displaying.

Re: Get data from previous period based on from and to date

Sam_SAS — Wed, 24 Nov 2021 14:50:17 GMT

If the seasons are predefined, you could assign the date ranges as a custom category and use the values of that as a filter.

I am out of the office for the Thanksgiving holiday but I will try to experiment with this later if you'd like.

Sam

Re: Get data from previous period based on from and to date

Dominik4 — Thu, 25 Nov 2021 12:18:03 GMT

Thank you for you offer. I think it is important to have a break from work. You can enjoy Thanksgiving holiday 🙂

Currently the users do 2 reports (one of each date range) and put it in excel together. I have the feeling that they do this since years so no need to hurry I guess. Nevertheless I try to improve the work process.

How could a custom category help? In this case I would have two filters (custom category and the normal period filter). Both can not apply at the same time so I need some kind of "or condition" if possible. But then I have an similar problem as before?

Re: Get data from previous period based on from and to date

Sam_SAS — Mon, 29 Nov 2021 14:37:48 GMT

Sorry, when I give this further thought I don't think a custom category is helpful for what you need. It works fine to select the time periods but cannot really be used to calculate the previous year's values.

It's possible that some very complex expression might work, but it would not be straightforward.

Sam

Re: Get data from previous period based on from and to date

Dominik4 — Wed, 01 Dec 2021 06:35:44 GMT

Thank you Sam for your efforts 🙂

I guess in the end I create a custom data source.

Re: Get data from previous period based on from and to date

Sam_SAS — Wed, 01 Dec 2021 14:41:46 GMT

Modifying your data source is often the best option, if it is possible for you. SAS DATA steps and SQL syntax are more powerful than the expression language in VA.

I hope it works out for you 🙂

Sam