Possible bug with ParallelPeriods and custom categories on date variable

[mike2468]

I have a number of crosstab reports that use the ParallelPeriod functions to provide year-on-year change for measures.

For example, I have a bunch of values in my datasource that are as-of 11/21/16, and a bunch more that are as of 11/23/15, and more as of 11/24/14, and so on. (We work on a 52-week calendar, which is why those dates shift about...they are all Mondays.) I need the change in the count of 2016 entries over the count of 2015 entries, and the change in 15 over 14, and so on. The ParallelPeriod function does this fine -- as long as I put that date variable in the rows or columns of the crosstab.

The calculations are of this form: ParallelPeriod(_Count_, 'row unique identifier'n, 'Date of count'n, _ByYear_, _ByYear_, -1, _Full_, {Date})

However, I can't surface my dates to the report consumer (this is a business requirement). I have to use a descriptive name instead. So I need my 2016 entries to have the heading "Fall 2016", and the 2015 entries to have the heading "Fall 2015," and so on.

So I create a custom category based on the as-of date. Everything with the date of e.g. "11/21/16" gets mapped to "Fall 2016," and the same with the other dates. Then I use that custom category in the crosstab instead of the date.

This used to work fine. This week I applied about 6 months worth of patches (including the VA patch V90005) to my VA/BI stack, and it stopped working. Now all those ParallelPeriod calculations return "missing," and I get the exclamation-point in the bottom-right corner of the crosstab. The mouseover of the exclamation point says:

"A mixture of date references caused missing values to be returned for a period calculation. If possible, consider creating the referenced dates by duplicating them from a common data item and adjusting properties of the duplicates such as the format."

I can switch the custom category for the as-of date that it's based on, and the parallel calculations work fine. Duplicating the date and changing the fomat and other properties of the duplicate as suggested in the mouseover text does not help.

Is this a bug -- perhaps something introduced in the latest round of patches? Any suggestions for a workaround?

[Sam_SAS]

Hi Mike,

Would it be possible for you to share a screenshot of your crosstab?

Are you on VA 7.3?

I did a quick test using a custom category like "Fall 2016" and an aggregated item using the formula you gave, and my crosstab is displaying without errors.

Thanks,

Sam

[mike2468]

Sure. I have attached three: one with the crosstab with the date variable (which works fine), one that shows the definition of the custom category based on the date variable, and one that shows the crosstab using the custom category instead of the date variable (which doesn't work).

According to Help->About in the Designer I am on 7.3 Hotfix 5 with a build date of Oct 3.

The specific formula for the "Change" measure is this:

ParallelPeriod(_Sum_, 'Measure'n, 'my date variable'n, _ByYear_, _ByYear_, 0, _Full_, {Date}) -

ParallelPeriod(_Sum_, 'Measure'n, 'my date variable'n, _ByYear_, _ByYear_, -1, _Full_, {Date})

and the % change is just the change divided by the last-year count. 

It does not seem to matter what format my date variable has. 

Thanks for the response. 🙂

[Sam_SAS]

Thanks Mike.

I can't replicate the error on my test machine here, but it is an earlier build of 7.3.

Why is it that you specify _ByYear_ as the inner interval for your ParallelPeriod operator? This will aggregate the values for the entire year.

I am wondering if things would be a lot simpler if instead of a custom category, you used the "Year, Quarter" format for the date variable. Would this satisfy the customer's requirement? If so, then I believe you would want to put _ByQuarter_ as the inner operator for ParallelPeriod, like so:

ParallelPeriod(_Sum_, 'Measure'n, 'my date variable'n, _ByQuarter_, _ByYear_, 0, _Full_, {Date}) -
ParallelPeriod(_Sum_, 'Measure'n, 'my date variable'n, _ByQuarter_, _ByYear_, -1, _Full_, {Date})

_Inferred_ would also work.

Would that work?

[mike2468]

It doesn't really matter...there are only actually five dates (one per year) in the entire dataset. So whether I aggregate it by quarter or year makes no difference.

The reasons for this are complex but boil down to the fact that we work on a 52- or sometimes 53-week calendar. I have to filter my extract based on the actual comparison dates and then assign a synthetic date to each year of data...it happens to be September 1 in my report, but that could be anything. So the current set of data was extracted on 11/28/16, but I have to be able to calculate the change over the data from 11/30/15. In the extract I give the 11/28/16 data the "count date" of 9/1/16, and the 11/30/15 date the "count date" of 9/1/15. It is an ugly hack. 

If SAS could calculate change across variables that aren't dates, this would be a lot easier. This is a major shortcoming in the product IMO...Tableau and even Excel can do this with a couple of clicks. But I digress.

"Year, Quarter" would not work (Fall 2016 has specific meaning for my consumers, and 2016, Q3 is not the same). At a push I could just use "Year" for the date format and specify elsewhere in the report that it is for fall. But this is not really satisfactory either. 

I am not surprised that it works on an earlier build. It worked on my instance until I updated it last week. I think that it may have been introduced with an update between about March/April (when I last updated) and hotfix 5. What version are you testing on?

I could probably gin up a simple dataset and instructions to reproduce the issue I'm seeing if that will help. If someone else can verify that it fails on another install of 7.3 hotfix 5 and it still works on yours, that would support my suspicion that it may have been a regression introduced in the last six months or so. 

[Sam_SAS]

I see, the _ByYear_ makes sense under those circumstances.

I am going to see if I can get someone from the development team to look at your issue. If there is a regression on the hotfix then we'll want to get to the bottom of it.

[mike2468]

Thanks Sam. Happy to assist in any way I can.

[Sam_SAS]

Mike, you should be hearing from someone at SAS shortly if you haven't already. I understand we have reproduced the issue on our end.

Thanks so much for your assistance!

Sam