topic Re: SAS Visual Analytics Edit - Calculated Data with Condition in SAS Visual Analytics

SAS Visual Analytics Edit - Calculated Data with Condition

Rian0126 — Tue, 31 Aug 2021 09:46:13 GMT

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.

Re: SAS Visual Analytics Edit - Calculated Data with Condition

Sam_SAS — Tue, 31 Aug 2021 13:10:07 GMT

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 .

Re: SAS Visual Analytics Edit - Calculated Data with Condition

Rian0126 — Wed, 01 Sep 2021 07:56:07 GMT

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.

Re: SAS Visual Analytics Edit - Calculated Data with Condition

Sam_SAS — Wed, 01 Sep 2021 13:08:48 GMT

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.

Re: SAS Visual Analytics Edit - Calculated Data with Condition

Rian0126 — Wed, 01 Sep 2021 14:47:08 GMT

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)"?

Re: SAS Visual Analytics Edit - Calculated Data with Condition

Sam_SAS — Wed, 01 Sep 2021 15:44:22 GMT

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

Re: SAS Visual Analytics Edit - Calculated Data with Condition

Sam_SAS — Wed, 01 Sep 2021 15:57:29 GMT

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.

Re: SAS Visual Analytics Edit - Calculated Data with Condition

Rian0126 — Thu, 02 Sep 2021 00:45:57 GMT

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