BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rian0126
Obsidian | Level 7

Hi, 

Is it possible to create a calculated data for example, a sum of distinct Alert ID but with a condition that Alert Status is 'Closed' only? I need this as a calculated data and not under Filter because filtering cannot combine two conditions of data to create a sum. Thank you.

 

Rian0126_0-1630403138872.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Sam_SAS
SAS Employee

You can put conditions such as IF / ELSE in a calculated item.

 

You might use something like,

 

IF 'Alert Status'n = "Closed"

RETURN Distinct(_ByGroup_, 'Alert ID')

ELSE .

View solution in original post

7 REPLIES 7
Sam_SAS
SAS Employee

You can put conditions such as IF / ELSE in a calculated item.

 

You might use something like,

 

IF 'Alert Status'n = "Closed"

RETURN Distinct(_ByGroup_, 'Alert ID')

ELSE .

Rian0126
Obsidian | Level 7

Thank you @Sam_SAS . But is it possible to add at least two calculated/aggregated measures? For example, I already created an aggregated measure for Andorra and Australia and I like to add them together. I know it sounds silly for an example. But this is the simplest example to explain what I am trying to do for a complex filter. 

Rian0126_0-1630482561270.png

 

When I tried adding them together, the content is "missing" value. Is there a way to put together the sum of two filtered values?

Rian0126_1-1630482628707.png

 

For what I am actually doing right now, I need to add together the one NOT CAPTURED BY SVI Open Alerts filter (LAST_DISPOSITION_TIMESTAMP= Missing and ALERT_STATUS = "Closed" and "Suppressed") and SVI Handled Alerts filter (LAST_DISPOSITION_TIMESTAMP= NotMissing and ALERT_STATUS = "Active", "Closed" and "Suppressed"). So in essence 34 + 1,455 =1,489. See table below for reference (rows highlighted in yellow). I need to show 1,489 value:

 

Rian0126_2-1630482889775.png

Thank you.

Sam_SAS
SAS Employee
I believe that adding a missing value and any value will result in a missing value. So you may want to return zero rather than missing.
Rian0126
Obsidian | Level 7

But for the case of the earlier example I did for Andorra and Australia, there should be number when you add those two values right? How come it's "(missing)"?

Sam_SAS
SAS Employee

Calculated items are evaluated for every row in the query, so I think a nonmissing value would only be returned if Australia AND Andorra match your conditions.

 

That said, I did an experiment returning 0 for calculated items and I got an unexpected result:

 

distinct.gif

The first table and key value use calculated items that return Missing. The second pair of object use items that return zero. The table results look good, but the key value unexpectedly returns 0.

 

I think that Key Value cannot aggregate the distinct value aggregated measures, because that is effectively a nested aggregation? I think we probably should not allow an aggregated measure to be assigned to Key Value, but I'll check that with the dev team.

 

One workaround to this to would be precalculate your conditioned distinct values as part of your data prep. If they are precalculated, they will be regular measures and should display just fine in Key Value. On the down side, they will not be responsive to data filters, etc.

 

Sam

Sam_SAS
SAS Employee

Actually, Key Value works fine for a basic Distinct calculation. So I am not totally sure what's up here. For the key value in your report, you could just display the distinct count of Open Alerts and filter that object to include only Andorra + Australia. This should yield the right number.

 

Then use your calculated item that returns 0 to get the right values in your table.

Rian0126
Obsidian | Level 7

I see. Thank you very much for your explanation @Sam_SAS 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 7 replies
  • 3035 views
  • 2 likes
  • 2 in conversation