Focal Point
[SOLVED] Selective WHERE

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/743107925

September 17, 2010, 04:40 PM
texgator
[SOLVED] Selective WHERE
In the sample code below, I want the YTDSALES field to disregard the WHERE selection for MONTH EQ '08' i.e. I want the WHERE MONTH EQ '08' to apply to the MTDSALES and BUDGETSALES fields but for YTDSALES field, I want it to add up values WHERE MONTH LE '08' or disregard the MONTH EQ '08' statement all together. Any suggestions on how this can be achieved?


TABLE FILE SALES
SUM
MTDSALES
YTDSALES
BUDGETSALES
BY REGION
BY COSTCENTER
WHERE COSTCENTER EQ 'ABC'
WHERE MONTH EQ '08'
END

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


WebFOCUS 7.6.10
Windows
all output (Excel, HTML, PDF)
September 17, 2010, 05:06 PM
Tom Flynn
Pretty simple. I would assume(probably incorrectly) that you are "NOT" hard-coding the Month, so I will assume it is another ampere variable, along with a Year(maybe?):

TABLE FILE SALES
SUM
  COMPUTE XMTDSALES/P13.2MC    = IF YEAR EQ '&YEAR' AND MONTH EQ '&MONTH' THEN MTDSALES    ELSE 0; AS 'MTDSALES'
  COMPUTE XYTDSALES/P13.2MC    = IF YEAR EQ '&YEAR' AND MONTH LE '&MONTH' THEN YTDSALES    ELSE 0; AS 'YTDSALES'
  COMPUTE XBUDGETSALES/P13.2MC = IF YEAR EQ '&YEAR' AND MONTH EQ '&MONTH' THEN BUDGETSALES ELSE 0; AS 'BUDGETSALES'
  COMPUTE FLAG/A1              = IF XMTDSALES EQ 0 AND XYTDSALES EQ 0 AND XBUDGETSALES EQ 0 THEN 'N' ELSE 'Y'; NOPRINT
BY REGION
BY COSTCENTER
WHERE COSTCENTER EQ 'ABC';
WHERE TOTAL FLAG EQ 'Y';
-* WHERE MONTH EQ '08'

END


WHERE statements end with a semi-colon, though, until IBI does some code-tightening, it will work without one.


hth

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
September 17, 2010, 05:12 PM
texgator
Yes, I am using amper variables. This should work. Thanks!


WebFOCUS 7.6.10
Windows
all output (Excel, HTML, PDF)