Hi All,

I have a requirement where I need to sort the data within the across field


Name 2015 Q1 2015 Q2 2015 Q3 2015 Q4
Name1 1.62% 1.45% 1.32% 1.45%
Name2 1.32% 1.45% 1.21% 1.45%
Name3 1.45% 1.21% 1.48% 1.62%
Name4 1.65% 1.32% 1.56% 1.70%

In the above example, I need to sort the latest quarter data ( here 2015 q4) in descending order. So the data with 1.70% is on top followed by 1.62% and the others. This is a calculated field.



Thanks
Deepa

This message has been edited. Last edited by: <Emily McAllister>,

Try ACROSS HIGHEST FIELDNAME

...
BY HIGHEST TOTAL ComputedField
BY Name
...
ACROSS YearQuarter

quote:

Try ACROSS HIGHEST FIELDNAME

will order the YrQtr from the highest (2015 Q4, 2015 Q3, 2015 Q2, 2015 Q1), not the values under them.

Wep as the solution, sorry BabakNYC

If you are sorting the values, then you will need to create a define/compute field and add it to the by fields.

Shru,
In fact you want ALL your output to be sorted by your Q4 values.
Once you created the ACROSS you do not have access to the Q4 data in order to sort, so you should create them before hand.
I would create a DEFINE field for the Q4 data, say MYQ4 and then issue:
SUM MYQ4 NOPRINT
BY TOTAL MYQ4 NOPRINT
BY NAME
SUM calculated_data
BY TOTAL MYQ4 NOPRINT
BY NAME ACROSS QUARTER
...
Using the CAR file as an example:

  
-* File shru02.fex
DEFINE FILE CAR
S5/D6=IF SEATS EQ 5 THEN RCOST ELSE 0;
END
TABLE FILE CAR
SUM S5 NOPRINT
BY HIGHEST TOTAL S5 NOPRINT
BY COUNTRY
SUM RCOST
BY HIGHEST TOTAL S5 NOPRINT
BY COUNTRY
ACROSS SEATS
END

Slight modification of Daniel code to get the max value of across field.

TABLE FILE CAR
SUM
COMPUTE MAX_CNTRY/A50=MAX.COUNTRY; NOPRINT
SUM
COMPUTE SS/I11=IF COUNTRY EQ MAX_CNTRY THEN SALES ELSE 0; NOPRINT
BY HIGHEST SS NOPRINT
BY MODEL
SUM
SALES AS ''
BY HIGHEST SS NOPRINT
BY MODEL
ACROSS COUNTRY
END

Hope this helps.
-Ram

Ram,
...
BY TOTAL HIGHEST SS NOPRINT
...

Thanks Daniel.