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,
InfoAssist 7.7.3 Windows, All Outputs
January 16, 2017, 03:06 AM
Dave
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. ;-)
_____________________ WF: 8.0.0.9 > going 8.2.0.5
January 19, 2017, 11:35 AM
Tamra
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
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 BuildersThis message has been edited. Last edited by: Tamra,
WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
January 19, 2017, 12:46 PM
Greenwood
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.
January 20, 2017, 01:24 PM
Tamra
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 BuildersThis message has been edited. Last edited by: Tamra,
WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
January 24, 2017, 09:27 AM
Tamra
Missing values can be represented with an additional attribute in the Master File Description ( Synonym )