Dead end on relative period function

[VictorM]

Dear all,

I'm refering to an old thread I've created some time ago that solved partially my problem.

https://communities.sas.com/t5/SAS-Visual-Analytics/Relative-period-function-weird-behaviour/m-p/476...

I'm back on this subject because I am know stuck with another dead end behaviour.

I have my graph and my cross table showing my n-1 sales, current sales and budget for 2019, for a selected month.

I can select the month in a dropdown list.

So let's assume I select "January 2019", considering my previous post just above, I have to configure the "relative period" aggregated data to "IgnoreTimeFiltering" otherwise the filter would get rid of my January 2018 sales (n-1) data.

So far so good....

Except, we're now in February 2019, I still have selected "January 2019" in my dropdown list because I want to see how my sales look like for last month, compared to the budget and to last year January 2018 sales.

Well, the "relative period" function returns the sales of February 2018 automatically, not January, probably considering the current date.

So the point is : Either I enable time filtering to tell the system "show me January 2019" and my relative period does not show up at all, or I IGNORE time filtering to show it up BUT it only takes into account the relative period based on the current date, not the filter I selected.

Any idea would be very appreciated...

Thank you

[Sam_SAS]

Hello Victor,

Is it possible you are using the wrong operator?

You say that you want a selection of January 2019 to return the value for January 2018. The operator that would do this is ParallelPeriod.

I find the operator names very confusing, myself!

Let us know if that helps,

Sam

[VictorM]

Good question. I tested with both, both have the same behavior and returns the same values.... does not help.

[Sam_SAS]

Can you show the expression for your aggregated item?

[VictorM]

here it goes : 

ParallelPeriod(_Sum_, 'SalesQty'n, _IgnoreAllTimeFrameFilters_, 'DateCalc'n, _ByMonth_, _ByYear_, -1, _Full_, {Date})

SalesQty is my sales of 2019 or 2018, that are recorded every month, DateCalcl format is 01012018,01022018,01032018... (european format).

Relative period works and behave the same way.

RelativePeriod(_Sum_, 'SalesQty'n, _IgnoreAllTimeFrameFilters_, 'DateCalc'n, _ByMonth_, -12, _Full_, {Date})

[Sam_SAS]

Ah yes, it didn't occur to me that you could use -12 with RelativePeriod to get the previous year.

It is very curious that it is affected by the current month, however. Would it be possible for you to share a screen capture of your report?

Thanks,
Sam

[VictorM]

Here it goes, I have taken the ParallelPeriod Function that I called "Sales2018". The SalesQty is the sales statistic column as explained above. Function used is :

ParallelPeriod(_Sum_, 'SalesQty'n, _IgnoreAllTimeFrameFilters_, 'DateCalc'n, _ByMonth_, _ByYear_, -1, _Full_, {Date})

As you can see I have selected the data (DateCalc) in the dropdown list for January 2019. Now the problem is that the Sales2018 function returns the figures of FEBRUARY 2018. I had to blur the data but it would not have meant a lot to you anyway.

Now If I apply timefiltering my Sales2018 is removed from my graph...

ParallelPeriod(_Sum_, 'SalesQty'n, _ApplyAllFilters_, 'DateCalc'n, _ByMonth_, _ByYear_, -1, _Full_, {Date})

[Sam_SAS]

Thanks Victor, I will try to replicate this usecase later today or tomorrow. The backend for my test machine is down at the moment.

You definitely do want the _IgnoreTimeFilters_ option, because the expression will return missing values otherwise when a single month is selected.

The behavior you are seeing is a bit odd, so you might try building a simplified version of the report from scratch and see if you can replicate the issue.

I guess the other thing to look at is, is Datecalc a calculated item? Is there something odd about its expression?

Thanks,
Sam

[Sam_SAS]

I can't seem to replicate the behavior you are seeing.

Here is my test, where Calculated Item 1 has this expression:

ParallelPeriod(_Sum_, 'Frequency'n, _IgnoreAllTimeFrameFilters_,
'Calculated Item 2'n, _ByMonth_, _ByYear_, -1, _Full_, {Date})

Here we have no selection in the drop-down list:

And here we have January 2019 selected:

The calculated date here was just to get a format similar to yours, but I got the same result with a basic date variable with MMMYYYY format.

In your report, does Sales2018 give the correct value when you clear the drop-down list selection?

Ultimately you might want to take this to SAS Technical Support to figure out what's going on.

[VictorM]

Interesting, i will try to replicate with basic data just like you did. Will let you know.

Thanks a lot !

[VictorM]

Hello Sam,

I was able to reproduce the exact same weird behaviour with simple data.

As you can see below, when I select "January 2019", the RelativePeriod function returns "February 2018". I insist on the fact that until 1st Febrary the function was returning January 2018 which makes me think that it is based on the current date...

[Sam_SAS]

Very strange!

What happens when you select 01FEB2019 instead of 01JAN2019? Also, does 01DEC2018 give you missing values or does it give you the values from 01JAN2018?

Probably we are going to want you to open a track with SAS Technical support.

Thanks,
Sam

[VictorM]

What happens when you select 01FEB2019 instead of 01JAN2019?

My sales data is updated accordingly, but the Sales2018 does not change.

Also, does 01DEC2018 give you missing values or does it give you the values from 01JAN2018?

Same behaviour, my sales data of Dec 2018 is displayed but the "ParallelPEriod" function returns February 2018, again.

How do I proceed to open a ticket ?

[Sam_SAS]

Thanks Victor. 

To create a track or otherwise contact Tech Support, go here:

https://support.sas.com/en/technical-support/contact-sas.html

Sam