I am trying to write a simple expression in my compute expression builder and I cannot figure out how to write it, it keeps telling me it is not a legal expression. I tried with the Parentheses and without. Can someone help me out?

(IF POAGE GT '0' AND LE '30' THEN '0-30' )
ELSE
(IF POAGE GT '30' AND LE '60' THEN '31-60' )
ELSE
(IF POAGE GT '60' AND LE '90' THEN '61-90' )
ELSE
(IF POAGE GT '90' AND LE '120' THEN '91-120' )
ELSE
(IF POAGE GT '120' AND LE '150' THEN '121-150' )
ELSE
(IF POAGE GT '150' THEN '150+')

Remove the parentheses and use ELSE IF. Make sure to end with a ; as well.

quote:

Originally posted by .eric:
Remove the parentheses and use ELSE IF. Make sure to end with a ; as well.

Like this? Still does not work.

IF POAGE GT '0' AND LE '30' THEN '0-30'
ELSE
IF POAGE GT '30' AND LE '60' THEN '31-60'
ELSE
IF POAGE GT '60' AND LE '90' THEN '61-90'
ELSE
IF POAGE GT '90' AND LE '120' THEN '91-120'
ELSE
IF POAGE GT '120' AND LE '150' THEN '121-150'
ELSE
IF POAGE GT '150' THEN '150+'

I assuming you're storing that into an alpha right correct?

Also I think you need to move the ' around the numbers in your IF statement, if they are number values webfocus will error.

quote:

Originally posted by .eric:
I assuming you're storing that into an alpha right correct?

Also I think you need to move the ' around the numbers in your IF statement, if they are number values webfocus will error.

Yes I have it set as an A20V format.

What ' should I remove? Around the actual values I am searching for? Or around the output? (Ex. 0-30)

Tried this.... still no go.

IF POAGE GT 0 AND LE 30 THEN '0-30'
ELSE
IF GT 30 AND LE 60 THEN '31-60'
ELSE
IF GT 60 AND LE 90 THEN '61-90'
ELSE
IF GT 90 AND LE 120 THEN '91-120'
ELSE
IF GT 120 AND LE 150 THEN '121-150'
ELSE
IF GT 150 THEN '150+'

Just noticed, should be:

IF POAGE GT 0 AND POAGE LE 30 THEN '0-30'

Need POAGE on both sides of the AND.

Ok I entered it like below and it took it, but when I ran the report everything came back as 150+ days. I know that is not right, why is it thinking everything is over 150 days?


IF POAGE GT 0 AND POAGE LE 30 THEN '0-30'
ELSE
IF POAGE GT 30 AND POAGE LE 60 THEN '31-60'
ELSE
IF POAGE GT 60 AND POAGE LE 90 THEN '61-90'
ELSE
IF POAGE GT 90 AND POAGE LE 120 THEN '91-120'
ELSE
IF POAGE GT 120 AND POAGE LE 150 THEN '121-150'
ELSE
IF POAGE GT 150 THEN '150+'

That might be hard to determine w/o looking at the data. Is POAGE an integer or decimal field?

POAGE is a defined field, D5 is the format.

Here are my defines:

DEFINE FILE T_PROD_PO
DATETODAY/HYYMDS=HGETC(10, DATETODAY);
POAGE/D5=HDIFF(DATETODAY, ORDERDATE, 'DAY', POAGE);
END


Here is my report code:

TABLE FILE T_PROD_PO
SUM
CNT.PONUM AS 'COUNT,PONUM'
BY ACCOUNTNUM
BY TOTAL COMPUTE POAGEGROUP/A20V = IF POAGE GT 0 AND POAGE LE 30 THEN '0-30'
ELSE
IF POAGE GT 30 AND POAGE LE 60 THEN '31-60'
ELSE
IF POAGE GT 60 AND POAGE LE 90 THEN '61-90'
ELSE
IF POAGE GT 90 AND POAGE LE 120 THEN '91-120'
ELSE
IF POAGE GT 120 AND POAGE LE 150 THEN '121-150'
ELSE
IF POAGE GT 150 THEN '150+';
WHERE (( ACCOUNTNUM EQ '650126' OR '650127' OR '651000' ) AND ( T_PROD_PO.STATUS NE 'CAN' )) AND (( RECEIPTS EQ 'NONE' ) AND ( TOTALCOST NE .00 )) AND ( T_PROD_WORKORDER.STATUS NE 'CAN' OR 'CLOSE' );

quote:

BY TOTAL COMPUTE POAGEGROUP/A20V

This is taking the total of POAGE and using that in your compute, you probably want to change this compute to go into your SUM section so it will run for each account number.

quote:

Originally posted by .eric:

quote:

BY TOTAL COMPUTE POAGEGROUP/A20V

This is taking the total of POAGE and using that in your compute, you probably want to change this compute to go into your SUM section so it will run for each account number.

Still doesn't work when I do it like this.

TABLE FILE T_PROD_PO
SUM
CNT.PONUM AS 'COUNT,PONUM'
COMPUTE POAGEGROUP/A20V = IF POAGE GT 0 AND POAGE LE 30 THEN '0-30' ELSE IF POAGE GT 30 AND POAGE LE 60 THEN '31-60' ELSE IF POAGE GT 60 AND POAGE LE 90 THEN '61-90' ELSE IF POAGE GT 90 AND POAGE LE 120 THEN '91-120' ELSE IF POAGE GT 120 AND POAGE LE 150 THEN '121-150' ELSE IF POAGE GT 150 THEN '150+';
BY ACCOUNTNUM

What is the value of POAGE for ACCOUNTNUM? You are doing the calculation as part of a sort break and you will only get the last POAGE for each record for each account, assuming that there are more than one PONUM per account.

What you need to do is a PRINT first to see what the POAGE is for each PONUM per account.

That will explain what you are getting.

What you may want to do is make the compute a define and then sort on POAGEGROUP within account.

Probablly now have to put the paranthesis back, however, I've found when checking to create ranges its best to work high to low. And I'm assuming you've verified POAGE's value.

You could also make your code a little easier to read (and therefore maintain) by using FROM ... TO syntax -

IF POAGE FROM   0 TO  30 THEN    '0-30' ELSE
IF POAGE FROM  31 TO  60 THEN   '31-60' ELSE
IF POAGE FROM  61 TO  90 THEN   '61-90' ELSE
IF POAGE FROM  91 TO 120 THEN  '91-120' ELSE
IF POAGE FROM 121 TO 150 THEN '121-150' ELSE '150+';

Still think he needs to do it as a define. The way it is currently coded, he will only get the last POAGE for the account sort group and I don't think that is what he wants.

Rob

did you consider the use of group like

TABLE FILE PROD_PO
SUM
CNT.PONUM AS 'COUNT,PONUM'
BY PAOGE IN-GROUPS-OF 30 TOP 150
END

This should do it, the only problem might be the description.

Ginny,

I agree, but it does depend on whether he wants the define on the summed total of the days difference between order date and run date or individual values of course. Although why you'd want to sum the days difference by account I can't think.

If we knew more on what he was trying to do then we could make suggestions.

T

Rob,

Franks method is a very simple one and it will give a good output albeit with limited explanation of the grouping but it will give you what you want.

To achieve what I think you are asking, try this code. The first is Frank's method, the second gives the column titles that you are after -

DEFINE FILE GGORDER
  ORD_DATE/I8DMYY = ORDER_DATE;
  DAY_DIFF/I8     = DMY(ORD_DATE,31121997);
  ORD_AGE/A12     = IF DAY_DIFF FROM   0 TO 100 THEN '0-100'   ELSE
                    IF DAY_DIFF FROM 101 TO 200 THEN '101-200' ELSE
                    IF DAY_DIFF FROM 201 TO 300 THEN '201-300' ELSE
                    IF DAY_DIFF FROM 301 TO 400 THEN '301-400' ELSE
                    IF DAY_DIFF FROM 401 TO 500 THEN '401-500' ELSE '501+';
END
TABLE FILE GGORDER
WRITE CNT.DAY_DIFF
BY PRODUCT_ID
ACROSS DAY_DIFF IN-GROUPS-OF 100 TOP 500
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END
-RUN
TABLE FILE GGORDER
WRITE CNT.ORD_AGE AS 'Counts'
   BY PRODUCT_ID  AS 'Product'
ACROSS ORD_AGE    AS ''
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END

T

quote:

TABLE FILE PROD_PO
SUM
CNT.PONUM AS 'COUNT,PONUM'
BY PAOGE IN-GROUPS-OF 30 TOP 150
END

Thanks I used Frank's method and that will work just fine for me.

Thanks for everyone's help!

Rob M.
If you are insistent on getting the original code to work - then I'd recommend the foll., but personally I like Frank's and Tony's appproach much better:


IF ((POAGE GT '0') AND (POAGE LE '30'))
THEN '0-30'
ELSE IF ((POAGE GT '30') AND (POAGE LE '60'))
THEN '31-60'
ELSE IF ((POAGE GT '60') AND (POAGE LE '90'))
THEN '61-90'
ELSE IF ((POAGE GT '90') AND (POAGE LE '120'))
THEN '91-120'
ELSE IF ((POAGE GT '120') AND (POAGE LE '150'))
THEN '121-150'
ELSE IF (POAGE GT '150') THEN '150+'

Hope this helps.

Ira
wf538 aix 533

I also use Tony's (or actual your original method, but only when the groups are in different size like

0-250
251-350
351-500
500-1000
1001-2500
higher.