Hi,

I have a report in which average and sum is being calulated based on a column value.
I have used following(example):

DEFINE FILE MOVIES
NRATING/A10=RATING;
END
TABLE FILE MOVIES
PRINT
COPIES
LISTPR
WHOLESALEPR
DIRECTOR
BY NRATING NOPRINT
BY RATING
BY CATEGORY

ON RATING SUBTOTAL
SUM. LISTPR AS 'SUM'
ON NRATING SUBTOTAL
AVE. LISTPR
AVE. WHOLESALEPR AS 'AVE'
END


Now I have to apply conditional styling on LISTPR and WHOLESALEPR data values. Formula for this uses
Average calculated on the Subtotal for eg.

(Average LISTPR - Actual Value of LISTPR) / Actual Value of LISTPR.

To calculate it I need the Average calculated. How to capture the Average value for each column

Thanks
Deep



For this

Deep,

Use the ability to capture the values you need at table level -

...
SUM AVE.LISTPR NOPRINT
PRINT COPIES
      LISTPR/D12.2
      COMPUTE HIGH_LOW/D12.2 = (AVE.LISTPR - LISTPR) / LISTPR; NOPRINT
etc.

Tony is almost correct. Keep in mind that this has to be done at rating level, not table level.
The complete code for this example:

DEFINE FILE MOVIES
NRATING/A10=RATING;
END
TABLE FILE MOVIES
SUM 
     AVE.LISTPR NOPRINT
     AVE.WHOLESALEPR NOPRINT
BY NRATING NOPRINT
BY RATING
PRINT 
     COPIES
     LISTPR
     WHOLESALEPR
     DIRECTOR
     COMPUTE COND/D12.2 = ( AVE.LISTPR - LISTPR ) / LISTPR; NOPRINT
BY NRATING NOPRINT
BY RATING
BY CATEGORY
     
ON NRATING SUBTOTAL
     AVE. LISTPR
     AVE. WHOLESALEPR AS 'AVE'
     
ON RATING SUBTOTAL
     SUM. LISTPR AS 'SUM'
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
     DEFMACRO=COND0001,
     MACTYPE=RULE,
     WHEN=N10 LT -.5,
$
     DEFMACRO=COND0002,
     MACTYPE=RULE,
     WHEN=N10 LT .5,
$
     DEFMACRO=COND0003,
     MACTYPE=RULE,
     WHEN=N10 GE .5,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=DATA,
     COLUMN=N7,
     BACKCOLOR='RED',
     MACRO=COND0001,
$
TYPE=DATA,
     COLUMN=N7,
     BACKCOLOR=RGB(255 153 0),
     MACRO=COND0002,
$
TYPE=DATA,
     COLUMN=N7,
     BACKCOLOR='LIME',
     MACRO=COND0003,
$
ENDSTYLE
END

Hope this helps.

Hi
I have following code:

.....
TABLE FILE XYZ
SUM
A AS 'MTD '
b AS 'YTD '
BY TTL NOPRINT
BY Temp NOPRINT
BY ST_PROV_C NOPRINT
BY YR_I NOPRINT
BY MO_I NOPRINT
BY CURR_PROTO_GRP_N
BY CURR_PROTO_T
BY REF_CO_LOC_I
BY HIER_LVL_4_N
ACROSS Util_Type AS ''
ON TTL SUBTOTAL
AVE. A
AVE. B AS ' Average Usage'
ON Temp SUBTOTAL
SUM. A
SUM. B AS 'Total Usage'
ON CURR_PROTO_GRP_N SUBTOTAL
AVE. A
AVE. B AS ' Average Usage'

......

Now in the conditional styling for the columns MO_USE_Q and MO_YTD_USE_Q I want to do some kind of coloring based on the formula

(store type average usage - store actual usage) divided by (store type average usage)) * 100 is less than -10, then color red ....

NOTE:
a) store type average usage is the average fouund based on CURR_PROTO_GRP_N
b) store actual usage for different columns is sum ( A ), sum(B) for different values of Util_Type(across value)

My question is how to capture the average value?

Thanks
Deep

Deepa,
Really, the solution to your question is in the code that Tony and myself posted in the previous posts.
You will have to create the averages in the request body itself, in this case the best way is to do a so-called multiverb request.
So:

SUM
AVE.A NOPRINT
AVE.B NOPRINT
BY TTL NOPRINT
BY Temp NOPRINT
BY ST_PROV_C NOPRINT
BY YR_I NOPRINT
BY MO_I NOPRINT
BY CURR_PROTO_GRP_N

SUM
A AS 'MTD '
B AS 'YTD '
BY TTL NOPRINT
BY Temp NOPRINT
BY ST_PROV_C NOPRINT
BY YR_I NOPRINT
BY MO_I NOPRINT
BY CURR_PROTO_GRP_N
BY CURR_PROTO_T
BY REF_CO_LOC_I
BY HIER_LVL_4_N

And now, since you can't use formulas in the conditional styling, create a compute that will indicate what color is to be shown. In your case, it should be a compute that holds the formula, for instance:
COMPUTE SIGNAL=((AVE.A - A)/A)*100;
and then you can use the value of this field for coloring the MO_USE_Q and MO_YTD_USE_Q fields.

This should do the trick for you.

Hi,

The logic you gave is perfectly running when no across field is there. But in my code you can see one across field is there i.e., ACROSS Util_Type AS ''
Becoz of this I am not able to apply the logic as values are not getting retrieved.

Thanks
Deep

Deep

If you know upfront which across fields you will get (a limited number) you can define these as separate fields.

Suppose your across is a month value you can say

DEFINE FILE whatever
JANVALUE=IF MONTH EQ 'JAN' THEN VALUE ELSE 0;
FEBVALUE=IF MONTH EQ 'FEB' THEN VALUE ELSE 0;
etc
DECVALUE=IF MONTH EQ 'DEC' THEN VALUE ELSE 0;
TOTALVALUE=JANVALUE+...+DECVALUE;
END
TABLE FILE whatever
SUM 
JANVALUE
FEBVALUE
...
DECVALUE
TOTALVALUE
...

Now you will get control over the different columns.

Hi,

Let me put it another way. Consider following ouput:


CATEGORY

TYPE ELECTRIC WATER

GM 10.5 10.0
11.0 10.0

AVERAGE GM 10.75 10
TOTAL GM 21.5 20

TS 12.5 12.5
10.5 10.5


AVERAGE TS 11.5 11.5
TOTAL TS 23.0 23

Total Average 11.125 10.75
Total Sum 44.5 43


Now in teh above output I have to put conditional styling on columns (Electric and Water ---- it's an across field)

Formula to be used is

(Average Type(for GM and TS) - Actual value of Type corresponding to Electric and Water) / Actual value of Type corresponding to Electric and Water


e.g.,

if we consider GM

it should be for the first row

(10.75 - 10.5) / 10.5
etc.....

I am not able to use the average in teh formula because of Across field (CATEGORY)

Please help me!!!

Thanks
Deep

Deep,

As I mentioned above (and Gamp also) -

quote:

Use the ability to capture the values you need at table (or sort) level

and then you can use that column in your conditional styling.

We try to assist but you have to do a certain amount of thinking yourself!! Otherwise, as has been said before, we will be seeking payment for doing your job for you!!!

DEFINE FILE MOVIES
NRATING/A10=RATING;
END
TABLE FILE MOVIES
SUM AVE.LISTPR
BY NRATING NOPRINT
BY RATING
BY CATEGORY
PRINT COPIES
      LISTPR
      WHOLESALEPR
      DIRECTOR
BY NRATING NOPRINT
BY RATING
ACROSS CATEGORY
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  TYPE=REPORT, GRID=OFF, $
  TYPE=DATA, ACROSSCOLUMN=N2, COLOR=RED, WHEN=C1 GT LISTPR, $
  TYPE=DATA, ACROSSCOLUMN=N2, COLOR=GREEN, WHEN=C1 LT LISTPR, $
ENDSTYLE
END
-RUN

T

Thank a lot!!!!
Itz working now.

Deep