I'm attempting to perform a Count Distinct using InfoAssist version 7.7.03. I think the data source is a table in a database formatted similarly to a flat file (though I'm not 100% certain of this).

I'm trying to count distinct a tracking ID where the results column and the alert type column have certain values.

I created the formula in two steps:

1) A Define field as follows:

DEFINE OffHook = IF Result EQ 'Live Answer' OR 'Answering Machine' OR 'Answering Machine Hangup' OR 'Connected' AND AlertType NE 'I' THEN TrackingId ELSE 'NA'

2) A COMPUTE on the define field as follows:

COMPUTE UniqOffHook = CNT.DST.OffHook - 1

This formula is part of a report that contains 90-100 other DEFINE and COMPUTE formulas.

This formula seemed to work as expected for a short period of time and then inexplicably began returning elevated values (we determined that these are EXPIRED results that are not a part of our criteria for the Offhook definition).

This formula continues to work in two other reports with fewer DEFINE and COMPUTE fields.

Has anybody experienced similar issues? I feel like we need a better way to implement this logic, but we haven't come up with one yet (despite a lot of experimenting), so I welcome your suggestions.

One other note is that we cannot use filters to apply the conditions to apply the conditions because it would disrupt other calculations in the report.

Thank you very much in advance.

This message has been edited. Last edited by: Tamra,

I think the 'elevated' results are not because of the cnt.dst but because to the selection.
( which is probably bigger then you expect ).

...and

if you use MISSING instead of 'NA' you don't have to substract 1 in your compute. ;-)

Please review the following regarding "MISSING" data. This might be what you are referring to as expired or your 'NA' as Dave indicates above.

On the IBI Website - Technical Support Center the following explanation of MISSING can be found:
Missing Field Values

To get to the above URL.....
Technical Support Center
- click on Search Technical Documentation

Enter search criteria and WebFOCUS version for whatever you are looking for. ( You will need to be logged on to the TechSupport site.

Thank your for participating in the Focal Point Forum,

Tamra Colangelo
Focal Point Moderator
Information Builders

This message has been edited. Last edited by: Tamra,

Thank you Dave and Tamra.

I work with T, and she has asked me to post the following:

We tried the following and it didn’t work:

IF ALERTSENT.ALERTSENT.Result EQ 'Live Answer' OR 'Answering Machine' OR 'Answering Machine Hangup' OR 'Connected' AND ALERTSENT.ALERTSENT.AlertType NE 'I' THEN ALERTSENT.ALERTSENT.TrackingId ELSE MISSING

Error message -- > (FOC171) Type of calculation is not specified on left side:

MISSING ON = IF ALERTSENT.ALERTSENT.Result EQ 'Live Answer' OR 'Answering Machine' OR 'Answering Machine Hangup' OR 'Connected' AND ALERTSENT.ALERTSENT.AlertType NE 'I' THEN ALERTSENT.ALERTSENT.TrackingId ELSE MISSING

Error message -- >(FOC224) Syntax error: ON

We welcome additional input.

Thank you.

Does the master file description have MISSING=ON for the fields used in the report?

I will provide an example shortly

Thank your for participating in the Focal Point Forum,

Tamra Colangelo
Focal Point Moderator
Information Builders

This message has been edited. Last edited by: Tamra,

Missing values can be represented with an additional attribute in the Master File Description ( Synonym )

FIELDNAME=RETURNS, ALIAS=RTN, FORMAT=I3, MISSING=ON, $

Are you able to review your master to see if the fields that you are using contain this attribute?

Thank your for participating in the Focal Point Forum,

Tamra Colangelo
Focal Point Moderator
Information Builders

This message has been edited. Last edited by: Tamra,

Tamra:

Thank you for your responses.

We've reviewed the underlying code and determined that the attribute is not present, and we do not have the ability to edit the master file.

In light of this, we've decided to delete virtual field and not try to report on it after all.

(That said, if anyone has suggestions for how we might still be able to do this, please let us know)

Thank you for your engagement.