BookmarkSubscribeRSS Feed
trt
Fluorite | Level 6 trt
Fluorite | Level 6

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.

5 REPLIES 5
SAS_Mike
Obsidian | Level 7

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 screen shot.JPG
trt
Fluorite | Level 6 trt
Fluorite | Level 6
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
Lapis Lazuli | Level 10

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
Fluorite | Level 6 trt
Fluorite | Level 6

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
Lapis Lazuli | Level 10

Hi trt,

 

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

 

will let u know..

 

 

 

Thanks & Regards,

Teja Surapaneni.

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1766 views
  • 0 likes
  • 3 in conversation