I have searched and found some examples of getting a percentage against subtotals - but I have either not understood what the document was saying, or it has not worked.

I am trying to get a percentage of each person's mailbox size compared to their departments. I am using SQL pass-through to provide the fields. If I use the Compute with LAST Department - the first employee in the department always say 100% - so I am not getting the percentage on the Total of the Department.

Any help in pointing me in the right direction would be greatly appreciated

This message has been edited. Last edited by: <Kathryn Henning>,

Hi,

Try below code.

APP PATH IBISAMP
-RUN
TABLE FILE CAR
SUM
DEALER_COST AS 'D_C_COUNTRY'
BY COUNTRY NOPRINT
SUM
DEALER_COST AS 'D_C_CAR'
BY COUNTRY
BY CAR
ON TABLE HOLD
ON TABLE SET ASNAMES ON
END
-RUN
TABLE FILE HOLD
PRINT
D_C_CAR AS 'DEALER_COST'
COMPUTE PERCENTAGE_1/D20.2%=D_C_CAR*100/D_C_COUNTRY; AS 'PERCENTAGE'
BY D_C_COUNTRY
BY CAR
ON TABLE PCHOLD FORMAT HTML
END

Thanks,
Ram

This message has been edited. Last edited by: Ram Prasad E,

I reckon that this is the best use of WITHIN.

TABLE FILE GGSALES
SUM       DOLLARS
      PCT.DOLLARS WITHIN REGION
BY REGION
BY STCD
END

Hi KL and welcome to the Point.

I think that Ram missed the point of your request and missing out the fact that you are having issues with the Percentage using LAST, but we've all done that before!

Try this code, it hits the sample table EMDPATA so that the example is similar to what you're after - data context wise. I use SALARY instead of inbox size but the method is the same for what you require.

Hopefully your installation still has the sample files installed with the server. I know some Companies omit these but they are so useful when asking for help (giving examples and repros for faults) I cannot understand why they wouldn't want to install them? Perhaps I'm too long in the tooth and have seen how useful they are? ..... Anyway, good luck

-* WebFOCUS Version used: 8.0.06
DEFINE FILE EMPDATA
  FULLNAME/A100 = LASTNAME || (', ' | FIRSTNAME);
END
TABLE FILE EMPDATA
-* Get the total at the right sort level that you wish to use as the divisor
-* but NOPRINT it as we don't want to see it in the report
  SUM SALARY NOPRINT
   BY DEPT

-* Now we can reference that report field using column notation,
-* this is the first non sort field so the notation is C1
  SUM SALARY
      COMPUTE PERCENTAGE/D6.2% = SALARY / C1 * 100;
   BY DEPT
   BY FULLNAME

-* Let us show the total being used in the percentage calculation in a subtotal for the sort value
ON DEPT SUBTOTAL AS '*'

-* As we're using a computed percentage, we need to use RECOMPUTE on the grandtotal
-* Try it with and without to see the difference that it makes ....
ON TABLE RECOMPUTE

-* This line turns of paging essentially
ON TABLE SET LINES 999999

-* This line turns off the automatic page numbering and some of the leading blank line(s)
ON TABLE SET PAGE NOLEAD

ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
  SIZE=9, FONT=ARIAL, SQUEEZE=ON, GRID=OFF, $
ENDSTYLE
END

T

quote:

best use of WITHIN

Good call Alan, why do we (i.e. me!) forget these recent syntax inclusions?

However, what about the GRANDTOTAL? I've not been able to get the percentage correct but then it is the end of the week! No doubt you've got that licked as well! I know this is due to the WITHIN DEPT.

DEFINE FILE EMPDATA
  FULLNAME/A100 = LASTNAME || (', ' | FIRSTNAME);
END

TABLE FILE EMPDATA
  SUM SALARY
      PCT.SALARY/D6.2% WITHIN DEPT
   BY DEPT
   BY FULLNAME
ON DEPT SUBTOTAL AS '*'

-* None of these produce the correct percentage ....
-*ON TABLE SUBTOTAL
-*ON TABLE SUMMARIZE
-*ON TABLE RECOMPUTE
ON TABLE COLUMN-TOTAL

ON TABLE SET LINES 999999
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
  SIZE=9, FONT=ARIAL, SQUEEZE=ON, GRID=OFF, $
ENDSTYLE
END

T

Thanks for pointing it Anthony. I missed that requirment.

-Ram

Tony,

Your procedure works beautifully - thank you so much for explaining each step - it really helped in converting it to my file - especially explaining what the column notations are.

Thanks again.

KL

As the old saying goes -

"give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime"

Glad it helped you out

T

quote:

I missed that requirment

Hi Ram,

If I had a pound for each time I've started to reply and then decided to double check I understood what was being asked ........

We've all done and will continue to do it!

T