Hi

I'm trying to sort a field (APPN_YEAR) in Ascending order.

Objective is to get the data for the last 5 years; and APPN_YEAR is a dynamic field.


TABLE FILE BR_INV_HDR
SUM
BR_INV_HDR.BR_INV_HDR.INVOICE_AMT
BY HIGHEST 5 BR_INV_HDR.BR_INV_HDR.APPN_YEAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

Result looks as follows:
APPN_YEAR INVOICE_AMT
2017 93610332.14
2016 240328241.31
2015 226778167.30
2014 214523089.27
2013 214587383.42

However, I want the APPN_YEAR field to be sorted in ascending order.

Thank You

WebFocus App Studio - 8.1.05

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

BY LOWEST 5...

LOWEST 5 doesn't give me the desired output.

The data runs back in time and is displayed as follows:
APPN_YEAR INVOICE_AMT
1900 960626.70
2001 12287012.95
2002 91499635.79
2003 116905631.70
2004 128628263.04

I want to restrict the data to last 5 years only - starting from 2013 till 2017 as of now.

This year field is dynamic.

I don't know what you mean by "This year field is dynamic". I can't think of a more elegant solution than a two-pass one - retrieve data to a hold file, then report off the hold file:

TABLE FILE BR_INV_HDR
SUM 
BR_INV_HDR.BR_INV_HDR.INVOICE_AMT
BY HIGHEST 5 BR_INV_HDR.BR_INV_HDR.APPN_YEAR

ON TABLE HOLD AS HOLD_BR_INV_HDR
END
-RUN

TABLE FILE HOLD_BR_INV_HDR
SUM 
BR_INV_HDR.BR_INV_HDR.INVOICE_AMT
BY BR_INV_HDR.BR_INV_HDR.APPN_YEAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
-RUN

You have two choices as I think of
1:

TABLE FILE BR_INV_HDR
SUM 
 BR_INV_HDR.BR_INV_HDR.INVOICE_AMT
BY HIGHEST 5 BR_INV_HDR.BR_INV_HDR.APPN_YEAR
ON TABLE HOLD AS TMP1 FORMAT FOCUS
END 
-RUN

TABLE FILE TMP1
SUM  INVOICE_AMT
BY APPN_YEAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END 

2:

-* Assuming that you receive a Year as a parameter since you mentioned that it's a dynamic field
-SET &LST5YRS = &RCVYR - 5;

TABLE FILE BR_INV_HDR
SUM 
 BR_INV_HDR.BR_INV_HDR.INVOICE_AMT
BY BR_INV_HDR.BR_INV_HDR.APPN_YEAR
WHERE BR_INV_HDR.BR_INV_HDR.APPN_YEAR GE &LST5YRS
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END 

Thank You Francis and Martin for the solution.

That's what I needed!