How to conditionally aggregate data in Report Designer?

[trt]

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.

[SAS_Mike]

Hello trt,

I have been thinking about this one since you posted it yesterday.  Despite the attempts to created a calculated or aggregated field to achieve this, I was not able to.

That being said, have you tried creating a new column in Data Query using SQL?  I am definitely not the one to talk to about SQL expressions (as much as I woud like to learn), but it does give you an option to use the data from the existing table to calculate a new item there.  To add the column, press the plus bar on the last column number and then name it.  You can then press the SQL expression symbol and the pop up will allow you to create an expression.  It does have the option to click on functions to create it, but my understanding is that it will work by typing in the code, if you have that expertise.

Hope this helps.  I am a little stuck on doing this in Report Designer.

Mike

[trt]

Hey SAS_Mike,

Thanks for your answer. I guess you are right, it seems to me a bit difficult to do it directly from Report Designer, so instead I need to figure out a new column for Data Source (built in "Data Preparation/Builder", as you mentioned). However, I still havent figured out the best way to do it, so that once it gets aggregated, the formula works properly 🙂

[TejaSurapaneni]

Hello trt,

Obejct Id is Character or Numeric.....?

How you want your final result in.....

Object ID                 Value

   1                             1.5

   2                             1.5

   3                             3.2

   4                             4.3

TOTAL                     10.5

is this your final out....?

Thanks & Regards,

Teja Surapaneni.

[trt]

Hey Teja,

In original database table, ObjectID is Character value.

The output should have "10.5" value as one of the columns. Let's say for this example, I would need to have an output like this:

=======================
Day   |   Value (aggregated)
1     |     6.2 (1.5+1.5+3.2)
2     |     6.2 (1.5+1.5+3.2)
3     |     10.5 (1.5+1.5+3.2+4.3)
=======================

But the time period should be flexible, as the periods have to be chosen by the client who would use the report. So in that sense, e.g. if the period is chosen to be from Day 2 onwards, then

=======================
Day   |   Value (Aggregated)
2     |     3 (1.5+1.5)
3     |     7.3 (1.5+1.5+4.3)
=======================

[TejaSurapaneni]

Hi trt,

It is looking too complictaed in frontend. Anyway let me try,

will let u know..

Thanks & Regards,

Teja Surapaneni.