topic Missing Values in Periodic aggregation SAS VA in SAS Visual Analytics

Missing Values in Periodic aggregation SAS VA

Nmah — Wed, 12 Sep 2018 10:00:50 GMT

Hi,

I am trying to calculate % change in monthly "Write off amount" in my data. To do this, I am using the periodic aggregation as below:

{Periodic aggregate (Current month) - Relative Period Aggregate (previous month)}/ Relative Period aggregate (previous month).

My queries:

1. The formula for current month's aggregate works fine if I use "_Inferred_" as the interval for period. But it returns a missing value if I use "_ByMonth_". "Period" is a date variable formatted as monyy. Screenshot attached.

2. The same formula does not work for "Relative Period" aggregation. So if I use "Relative Period" aggregation, it returns a missing value while using "_Inferred_" or "_ByMonth_". Hence unable to output any percentage change. Screenshot attached.

Note: Variable "Period" is SAS date variable (includes year) formatted as monyy.

Please can someone help me understand where is the problem and why is one function working fine whereas another similar function is not.

Re: Missing Values in Periodic aggregation SAS VA

ballardw — Wed, 12 Sep 2018 18:57:16 GMT

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide some example input data, preferably in the form of data step code pasted into a code box, the actual results and the expected results.

Re: Missing Values in Periodic aggregation SAS VA

Nmah — Thu, 13 Sep 2018 09:04:54 GMT

Thanks for your reply..

It does not give an error just returns a missing value that's why I am unable to interpret.

But I think I have come to an understanding that this is because I am using the periodic aggregation in a "List Table" where "period" is not a data object in the table although it is being used as a dropdown control to display different months.

Hence the formula is working fine on my other tables which are "Cross tabs" and have "Period" as a data object in the report/visualisation.

Hence closing the thread.

Thanks again!