Assume we have the following table loaded as data source in SAS Visual Analytics:
======================= Day | objectID | Value 1 | 1 | 1.5 1 | 2 | 1.5 1 | 3 | 3.2 2 | 1 | 1.5 2 | 2 | 1.5 3 | 1 | 1.5 3 | 4 | 4.3 =======================
Now I want to get a sum of all distinct objectIDs over these 3 days (assumption is that value does not change over days). literary, I would like to get:
value_for_objectID_1 + value_for_objectID_2 + value_for_objectID_3 + value_for_objectID_4
which would be in this case 1.5+1.5+3.2+4.3=10.5
In form of a SQL query:
select sum(a.value) from (select distinct objectID, value from DATA_TABLE_ABOVE) a;
Since to my knowledge, SAS Visual Analytics does not offer SQL queries within chosen data source in the "Report Designer", how would one be able to aggregate value conditionally (in this case over distinct values of another column). Any hints/suggestions would be very helpful.