10 Replies Latest reply on Aug 24, 2018 1:04 PM by Okechukwu Ossai

# Calculating percentages % - v10.5

Hello,

I need to calculate percentages but I seem to be missing how to do it.  The attached 10.5 workbook includes three examples of what I'm trying to do and I don't know if I should be defining multiple parameters for each percentage calculation or using a lookup to another data source.  It seems like it should be fairly simple to divide one calculated number by another calculated number but it's not working for me.

The dashboards in the workbook explain what I want to do but so any and all help would be appreciated.

I'm happy to answer any questions,

~S

• ###### 1. Re: Calculating percentages % - v10.5

Conacher,

Which one is what you need?

Michael Ye

• ###### 2. Re: Calculating percentages % - v10.5

I'd like a separate view to place under the count table that displays:

 Division calculation that makes the %'s below which is what I want: 27/117 14/102 45/66 53/119 60/84 39/81 238/569
 % of total claims 5% 2% 8% 9% 11% 7% 42%

And similar for the other two dashboards

• ###### 3. Re: Calculating percentages % - v10.5

Hi conacher,

You may be best served by creating a calculated field for Litigated Cases. You would use similar logic to what you're putting on the Filters right now, so it would look something like:

IF

([Status] = "Open" OR [Status] = "Reopened")

AND

THEN 1

ELSE 0

END

Of course you'll need to tweak the above field to whatever the actual logic is to determine Litigated Cases. It will give you a 1 or 0 you can operate on like you currently are with Number of Records, so you can create another field called "% Litigated" and make that SUM([Litigated Cases])/SUM([Number of Records]). Format that as a percentage and you should be good.

• ###### 4. Re: Calculating percentages % - v10.5

Hi Conacher,

It should indeed be straightforward to divide one calculated number with another. However, the numbers are not calculated fields. They have been derived on a worksheet by applying different filter options. So, it is not possible to divide one with another. To do this, you will first have to create calculated fields for litigated claims and total claims. I've tried to do this by converting the filter options into conditional statements. This is a guide, you can modify it to suit what your objectives.

Litigated Claims Assumptions

• Date filter: July 2018 to July 2017. I assume this is last 12 months from the maximum date in the database
• Status filter: All status except 'closed'
• Claim Type filter: All claim types except claim types with no data
• Table name filter: Data

Total Claims Assumptions

• Date filter: July 2018 only. I assume this is the maximum (most recent) date in the database
• Status filter: All status except 'closed'
• Claim Type filter: All claim types except claim types with no data
• Table name filter: Data
• WCAB/ADJ# filter: All values including nulls

I converted these assumptions into 3 calculated fields.

Step 1: [Litigated Claims by Claim Type]

{FIXED [Type Of Claim]: SUM(IF NOT ISNULL([WCAB/ADJ#]) AND [Status] <> 'Closed'

AND [Open Claim Month] >= [Last 12 Months] AND [Open Claim Month] <= [Max Month]

AND [Table Name] = 'Data' THEN [Number of Records] END)}

Step 2: [Total Claims by Claim Type]

{FIXED [Type Of Claim]: SUM(IF [Table Name] = 'Data' AND [Status] <> 'Closed'

AND [Open Claim Month] = [Max Month] THEN [Number of Records] END)}

Step 3: [% of Total Claims in Litigation]

[Litigated Claims by Claim Type]/[Total Claims by Claim Type]

Everything works except the grand total percentage. This will require a separate calculation in a separate worksheet. I moved things around to align the dashboard objects to accommodate the Grand Total worksheet. Not perfect but may be the best option you have.

Step 4: [Grand Total]

{FIXED: SUM([Litigated Claims by Claim Type])}/{FIXED: SUM([Total Claims by Claim Type])}

See attached workbook. Since you have better background knowledge of your analysis, please feel free to adjust the assumptions.

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 5. Re: Calculating percentages % - v10.5

Thank you!   I don't see the attached workbook though.

• ###### 6. Re: Calculating percentages % - v10.5

You're welcome.

The attached workbook is at the bottom of the comment. See screenshot below.

I'm re-attaching it also. Could you please mark the answer as correct if it resolved your question.

1 of 1 people found this helpful
• ###### 7. Re: Calculating percentages % - v10.5

Odd, this is the third time I haven't see a workbook that people have attached.  This is what I see on Chrome 68.

• ###### 8. Re: Calculating percentages % - v10.5

It's interesting. I use Firefox and have not experienced the issue so far. Maybe you can try using another browser

• ###### 9. Re: Calculating percentages % - v10.5

By any chance can you email me your mocked up workbook?  saconacher@gmail.com

• ###### 10. Re: Calculating percentages % - v10.5

I've emailed it.