SAS Visual Analytics Edit - Calculated Data with Condition

[Rian0126]

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.

[Sam_SAS · Accepted solution]

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]

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. 

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

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:

Thank you.

[Sam_SAS]

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]

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]

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:

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]

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]

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