I'm trying to count the number of distinct orders I get in this result set, but I think it's counting the number of lines, not the number of orders.

 TABLE FILE SHIP2LIST
-*BY 'HOME_BR' AS 'BRANCH'
-*ON TABLE HOLD AS USXTRACT FORMAT FOCUS INDEX 'DIVISION' 'J1.PRODUCTS_US.PROD_CAT' 'J1.PRODUCTS_US.BRAND' 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' 
SUM 
     'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM' WITHIN TABLE
	 UNITS_PURCH AS 'U_PURCHASED'
     'J0.SALES_CURR_PREV_FY_US.PURCH_DOLLARS' AS 'P_DOLLARS'
     UNITS_RETURNED AS 'U_RETURNED'
     'J0.SALES_CURR_PREV_FY_US.RETURN_DOLLARS' AS 'R_DOLLARS'
     'J0.SALES_CURR_PREV_FY_US.GP_DOLLARS' AS 'GP_DOLLARS'
BY 'SHIP2LIST.SEG01.DIVISION' AS 'DIVISION'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_YEAR' AS 'FYEAR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_QUARTER' AS 'FQTR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_PERIOD' AS 'FPER'
BY 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' AS 'YR_MO'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_METHOD' AS 'ORD_METH'
BY 'SHIP2LIST.SEG01.REGIONAL_MGR_ID' AS 'REG_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.SALES_MGR_ID' AS 'SALES_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.PR_BR' AS 'BRANCH'
BY 'SHIP2LIST.SEG01.BRANCH_ABBREV' AS 'BRANCH_NAME'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_NUM'
...MORE BY FIELDS...
.
.
.

I'm trying to build a summarized table that I can use in a dashboard environment and then I can do calculations for % of orders. I've tried a couple different scenarios and none seem to be working.

I don't actually need the ORDER_NUM to be a part of the final summarized table - I just have it in this part of the procedure to be able to count it. All I ultimately need is the # of orders.

Any help is greatly appreciated.

Jeralee

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

SUM
'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM'
BY 'SHIP2LIST.SEG01.DIVISION' AS 'DIVISION'
SUM
'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM' WITHIN TABLE
UNITS_PURCH AS 'U_PURCHASED'
'J0.SALES_CURR_PREV_FY_US.PURCH_DOLLARS' AS 'P_DOLLARS'
UNITS_RETURNED AS 'U_RETURNED'
'J0.SALES_CURR_PREV_FY_US.RETURN_DOLLARS' AS 'R_DOLLARS'
'J0.SALES_CURR_PREV_FY_US.GP_DOLLARS' AS 'GP_DOLLARS'
BY 'SHIP2LIST.SEG01.DIVISION' AS 'DIVISION'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_YEAR' AS 'FYEAR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_QUARTER' AS 'FQTR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_PERIOD' AS 'FPER'
BY 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' AS 'YR_MO'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_METHOD' AS 'ORD_METH'
BY 'SHIP2LIST.SEG01.REGIONAL_MGR_ID' aS 'REG_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.SALES_MGR_ID' AS 'SALES_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.PR_BR' AS 'BRANCH'
BY 'SHIP2LIST.SEG01.BRANCH_ABBREV' AS 'BRANCH_NAME'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_NUM'

Hi Tom!

I wondered if a multiverb request would be the way to go.

I tried your code and here's the error I gotFOC1856) ONLY ONE DISTINCT FIELD IS ALLOWED IN AGGREGATION

Bad Copy and Paste by Me!

Take out the duplicate under the second SUM:

SUM
UNITS_PURCH AS 'U_PURCHASED'
'J0.SALES_CURR_PREV_FY_US.PURCH_DOLLARS' AS 'P_DOLLARS'
UNITS_RETURNED AS 'U_RETURNED'
'J0.SALES_CURR_PREV_FY_US.RETURN_DOLLARS' AS 'R_DOLLARS'
'J0.SALES_CURR_PREV_FY_US.GP_DOLLARS' AS 'GP_DOLLARS'
BY 'SHIP2LIST.SEG01.DIVISION' AS 'DIVISION'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_YEAR' AS 'FYEAR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_QUARTER' AS 'FQTR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_PERIOD' AS 'FPER'
BY 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' AS 'YR_MO'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_METHOD' AS 'ORD_METH'
BY 'SHIP2LIST.SEG01.REGIONAL_MGR_ID' aS 'REG_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.SALES_MGR_ID' AS 'SALES_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.PR_BR' AS 'BRANCH'
BY 'SHIP2LIST.SEG01.BRANCH_ABBREV' AS 'BRANCH_NAME'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_NUM'

It still doesn't like it.

(FOC1867) DST OPERATOR MUST BE AT THE LOWEST LEVEL OF AGGREGATION


So, I'm assuming that this means it wants it within the second SUM? If so, then what would I put in the first SUM? The other fields I am aggregating? I'll try that..

Hmm - no dice on that.

How about an actual counter? Would that be something to try? I have to believe there is a way to do this using the functions, though.

Thank you!

Jeralee,

Again, I don't go through everyone's code "exactly".

Take out your last sort:

BY 'J0.SALES_CURR_PREV_FY_US.ORDER_NUM'

Actually, try this:

 
TABLE FILE SHIP2LIST
SUM 
     'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM'
     UNITS_PURCH AS 'U_PURCHASED'
     'J0.SALES_CURR_PREV_FY_US.PURCH_DOLLARS' AS 'P_DOLLARS'
     UNITS_RETURNED AS 'U_RETURNED'
     'J0.SALES_CURR_PREV_FY_US.RETURN_DOLLARS' AS 'R_DOLLARS'
     'J0.SALES_CURR_PREV_FY_US.GP_DOLLARS' AS 'GP_DOLLARS'

BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_YEAR' AS 'FYEAR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_QUARTER' AS 'FQTR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_PERIOD' AS 'FPER'
BY 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' AS 'YR_MO'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_METHOD' AS 'ORD_METH'
BY 'SHIP2LIST.SEG01.REGIONAL_MGR_ID' aS 'REG_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.SALES_MGR_ID' AS 'SALES_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.PR_BR' AS 'BRANCH'
BY 'SHIP2LIST.SEG01.BRANCH_ABBREV' AS 'BRANCH_NAME

Tom

That's almost exactly where I started, but wihtout the WITHIN. I'm getting results but its still giving me a 1 on every line, even though they are on the same order.

As always - thank you for your patience with me!

Then try the 'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM' WITHIN DIVISION not TABLE...

That gives me the total number of orders in the result set. I'm looking for a result like this.

Order Product CNT.DST.Order
1 TIRES 1
1 TUBES 0
1 WHEELS 0
2 TIRES 1
3 TUBES 1


That way I can SUM the CNT.DST.Order column and get a total number of orders in that set...

Am I totally goofing this all up??

Hi everyone,

Any other suggestions on how I can accomplish this? Am I missing something really simple?

Thanks!
Jeralee

I am confused by your request. I would assume order number to be be unique for every order. Therefore a count of distinct order numbers from the TABLE would in fact be a count of the number of records in the table. No?

For example, in this query against the GGSALES sample table, field SEQ_NO is unique for every record. So the count of distinct SEQ_NO WITHIN TABLE will always be the same number, no matter which BY fields are used.

TABLE FILE GGSALES
 SUM 
  CNT.DST.SEQ_NO WITHIN TABLE
  UNITS
  DOLLARS
 BY REGION
 BY ST
 BY CATEGORY
 BY PCD
 BY PRODUCT
END

Hi Dan,

Actually there are multiple records per order because the data stores each line number separately. Therefore, I need to count each order number only once.

I thought this would be something easy to get but it's turning out to be more confusing than I expected.

Thank you!

Based on your sample output above, it appears you want the distinct count to appear for only the first record in a "set". You should use that "set" value to define your WITHIN clause for the CNT.DST. operator. Then you can use a COMPUTE to limit the resulting counts to the first record in each set. For example:

TABLE FILE GGSALES
 SUM
  CNT.DST.PCD/I5 WITHIN REGION
  COMPUTE CNT_DST_PCD/I5C = IF (REGION NE LAST REGION) THEN CNT.DST.PCD ELSE 0 ;
  UNITS
  DOLLARS
 BY REGION
 BY ST
 BY CATEGORY
 BY PRODUCT
END