SAS Visual Analytics: Missing values affecting Aggregations

eslna — Tue, 01 Nov 2016 16:07:04 GMT

I may or may not have found a bug in SAS VA.

I have columns that are coming from Oracle and colums that are calulated at the Data Query level.

Here are the calculated columns:

Column a: CASE WHEN (O_OVT_COMPTIME_FV.NC_PAYCODE IN ('Comp Time Earned', 'Comp Time Earned II')) THEN O_OVT_COMPTIME_FV.HOURS_EARNED END

Column b:CASE WHEN (O_OVT_COMPTIME_FV.NC_PAYCODE IN ('Overtime', 'Overtime SD', 'OT ADJ', 'OT SD ADJ')) THEN O_OVT_COMPTIME_FV.HOURS_EARNED END

Column c: CASE WHEN (O_OVT_COMPTIME_FV.NC_PAYCODE IN ('Regular', 'REG ADJ', 'REG SD ADJ', 'Regular SD')) THEN O_OVT_COMPTIME_FV.HOURS_EARNED END

On the Report, I have created 3 aggregate fields based on each of these columns, the aggregate fields are the following:

Sum [_ByGroup_] ('Column a'n)

Sum [_ByGroup_] ('Column b'n)

Sum [_ByGroup_] ('Column c'n)

I have a Table object(same thing happens in a crosstab) that is supposed to display these aggregate values by the group and if all 3 columns mentioned above have data, then the row is displayed with no problem.

However, I have noticed that records that have missing values in, say Column a, wont show up in the table object(or the crosstab) once I add Column a to the object. If i remove column a from the table object, the row shows up again.

Something about that SUM is causing the record to dissapear from the table object, and no, i dont have any filters on that particular object.

I tried doing an IF check in that aggregate field, but it will not let me check "Column a" against missing or zero, it forces me to use "Sum [_ByGroup_] ('Column a'n)" in the comparison.

I have also tried adding an Else 0 to all the column calculations in the data query but that doesnt help either.

Any ideas?

Re: SAS Visual Analytics: Missing values affecting Aggregations

eslna — Tue, 01 Nov 2016 19:17:54 GMT

Update: I decided to move those columns and calculate/generate them at the database level. Still same issue.

Re: SAS Visual Analytics: Missing values affecting Aggregations

eslna — Wed, 02 Nov 2016 18:59:01 GMT

I feel like I am talking to myself since no one responded but basically what I am trying to do, which is filter a table object by an aggregated value, is not possible in SAS VA 7.1, but good news is it IS possible in 7.3 judging by this document: http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#p0989k7vl59ugbn1183c1u36lvcn.htm

So i will mark this ticket as solved and work on getting our environment upgraded.

topic Re: SAS Visual Analytics: Missing values affecting Aggregations in SAS Visual Analytics

SAS Visual Analytics: Missing values affecting Aggregations

Re: SAS Visual Analytics: Missing values affecting Aggregations

Re: SAS Visual Analytics: Missing values affecting Aggregations