I have a report that does a CNT.DST.FieldName within a BY field -- it was created in the GUI in 8203 but it won't work in 8206.

Here's the code:

 
TABLE FILE ENTERPRISE_ANALYTICS/MST_POLICY_ITEMS_PREMIUM
SUM CNT.DST.MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.POLICY_NUMBER AS 'Policy Count'
CNT.DST.MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.POLICY_NUMBER WITHIN MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.STATE_ABBR NOPRINT
COMPUTE c_percent_of_total/D12%=C1/C3 * 100 ; AS '% of Total'
MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.GROSS_AMOUNT/P20M AS 'CMP DWP'
COMPUTE c_percent_prem/D12%=PCT.MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.GROSS_AMOUNT ; AS '% of Total'
DST.J002.MST_POLICY_INFORMATION_ACTIVE.POLICY_TOTAL_INSURED_VALUE/P20M AS 'TIV'
COMPUTE c_percent_tiv/D12%=PCT.J002.MST_POLICY_INFORMATION_ACTIVE.POLICY_TOTAL_INSURED_VALUE ; AS '% of Total'
BY MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.STATE_ABBR NOPRINT
BY d_county_group AS 'County Group'
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.NEW_DIRECT_WRITTEN_PREMIUM_FLAG_YTD EQ 1;
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.HOLD_FLAG EQ 0;
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.POLICY_TYPE EQ 'CMP';
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.STATE_ABBR EQ 'LA';
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.DIRECT_WRITTEN_PREMIUM_ACTIVE_DATE GE J003.MAINT_REPORTING_DATES.BEGINNING_OF_LAST_YEAR;
ON TABLE PCHOLD FORMAT HTML
 

The error it gives me in 8206 is FOC1867 - Operator must be at the lowest level of aggregation. I found this post post in focal point, but when I try to create a compute field for CNT.DST.MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.POLICY_NUMBER I get a syntax error.

Anyone know of another work around or is this a case for tech support?

Thanks,
Deb

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

quote:

FOC1867 - Operator must be at the lowest level of aggregation

we have the same issue when i looked in the techsupport this is what i found.

This is working as designed. A WITHIN is the alternative to a multi verb request which allows summarization of values at different sort levels. The error message is indicating that DST must be at the lowest sort level only.

Please refer to the following link:
https://webfocusinfocenter.inf...s6/wf8206crlang.pdf, pages 70 - 72

The following error occurs if you use a multi-verb request, SUM DST.fieldname BY field PRINT fld BY fld (a verb object operator used with the SUM command must be at the lowest level of aggregation):
(FOC1867) DST OPERATOR MUST BE AT THE LOWEST LEVEL OF AGGREGATION

Curious. I guess this is code tightening between 8203 and 8206?

Still on 8008 here and it has always worked that way for me. This is another one of my pet peeves. Maybe we can get one of the Walters to explain why it works this way. The workaround is to do each count distinct in a separate TABLE and hold the files then use MATCH or JOIN to put the data into one file, at least, that is what I have always had to do.

My business users are gonna be so thrilled to have to rewrite their reports to use hold tables and joins!

I guess that's what they will have to do because I can't seem to get any other trick to work.

One of my other pet peeves is Count DST does not work in MATCH. Try it and you get (FOC1854) THE DST OPERATOR IS ONLY SUPPORTED IN TABLE REQUESTS. I which someone could explain that one as well.

I did solicit "The Walters" as John called them and this is the reply..

Even though there was no error prior to 8206, the behavior gave you the sum of the distinct values at the lowest level rather than the actual distinct values for the BY grouping. Look at this code:

-* Raw data
TABLE FILE CAR
PRINT SEATS
BY COUNTRY
BY CAR
END

-* Distinct for one BY
TABLE FILE CAR
SUM CNT.DST.SEATS
BY COUNTRY
END

-* Distinct for two BYs NOTE for ENGLAND it is 3.
TABLE FILE CAR
SUM CNT.DST.SEATS
BY COUNTRY
BY CAR
END

-* Distinct for two BYs with WITHIN FIRST BY
-* NOTE ENGLAND now is 4 because
-* You have 2 distinct seats for JAGUAR
-* 1 for JENSEN and 1 for TRIUMPH. The SUM WITHIN
-* simply adds them up which might not be what
-* the customer really wanted (meaning they wanted
-* to see the 3 from the previous request with one BY phrase
TABLE FILE CAR
SUM CNT.DST.SEATS WITHIN COUNTRY
BY COUNTRY
BY CAR
END

In order to avoid not getting what is expected, the FOC1867 is thrown.

Thanks Chuck, but for the way we've been using it, it has been correct -- we use it to get a percent of a sublevel within the greater level. We are looking to answer questions like: What percentage of cars have 2 types of seat configurations out of all cars in England (to use the CAR example)?
It is just a curious thing that it has been working for us when apparently it shouldn't. Thanks for checking with The Walters!