13 Replies Latest reply on Nov 15, 2018 3:23 PM by Paul Wachtler

# Capturing subtotal as a field for use in later calculations

How can I capture the subtotal of a column that I can then use in a calculation? For example in the picture below I have the Curr Yr Amt. The MM column in this diagram is fixed at 5. But suppose they were all different. I now want to divide the MM by the total of the Curr Yr Amt column. Any help is appreciated.

Thanks • ###### 1. Re: Capturing subtotal as a field for use in later calculations

Hi Sam,

To divide one of the values in MM by the Total of the Curr Yr Amt field you would do this:

sum([MM])/ Total(sum([Curr Yr Amt]))

That will take a single MM value and divide by the whole Curr Yr Amt column.  Let me know if you have any questions.

Best,

Paul

• ###### 2. Re: Capturing subtotal as a field for use in later calculations

Great! I will try it and let you know.

Thanks

• ###### 3. Re: Capturing subtotal as a field for use in later calculations

SUM({FIXED HCG Setting:SUM(MM)})/SUM({SUM(Curr Yr Amt)})

• ###### 4. Re: Capturing subtotal as a field for use in later calculations

Will this work on a hyper extract too?

• ###### 5. Re: Capturing subtotal as a field for use in later calculations

yes

• ###### 6. Re: Capturing subtotal as a field for use in later calculations

Yes. This works. I have not tried the other method  yet. Is there any way I can suppress column values except the subtotal? For example, in the screenshot below, only the total for the MM is shown. • ###### 7. Re: Capturing subtotal as a field for use in later calculations

Hi Sam - I'm not sure what you're asking.  Do you want to hide the other columns, other than Member Months?  Or do you want to hide all of the rows except for the Total?

Best,Paul

• ###### 8. Re: Capturing subtotal as a field for use in later calculations

Paul

Look at the Member Months and Normalized Risk Score columns. The cell values are hidden except for the totals

• ###### 9. Re: Capturing subtotal as a field for use in later calculations

Right, but I'm not clear on whether you want the other colums to look like that so you can only see subtotals or if you want values in those fields.

• ###### 10. Re: Capturing subtotal as a field for use in later calculations

Exactly as shown

• ###### 11. Re: Capturing subtotal as a field for use in later calculations

So the only thing I can think of is to go into the format menu for both your MM and NRS fields and try to change the font color to white, but keep it black for the subtotal values.

• ###### 12. Re: Capturing subtotal as a field for use in later calculations

Pretty smart ! Going back to the diagram,  There is a Risk Score associated with each row (not shown in this diagram). Each Risk Score is then divided by  the average of the entire Risk Score column to give you the Normalized Risk Score that is shown. Would I be creating a calculated field called Normalized Risk Score with the  formula:  sum([Risk Score])/ Total(AVG([Risk Score])).

• ###### 13. Re: Capturing subtotal as a field for use in later calculations

Hey Sam - I believe that formula would work.  Let me know if it does.  If not we can probably figure out a window calc.