Get data from previous period based on from and to date

[Dominik4]

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?

[Sam_SAS]

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

[Dominik4]

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?

[Sam_SAS]

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

[Dominik4]

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.

[Sam_SAS]

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

[Dominik4]

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?

[Sam_SAS]

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

[Dominik4]

Thank you Sam for your efforts 🙂

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

[Sam_SAS]

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