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

    Calculating percentages % - v10.5

    conacher

      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
          Michael Ye

          Conacher,

           

          Which one is what you need?

           

          Michael Ye

           

           

           

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

            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/11714/10245/6653/11960/8439/81238/569
            % of total claims5%2%8%9%11%7%42%

             

             

            And similar for the other two dashboards

            • 3. Re: Calculating percentages % - v10.5
              Eric Mathis

              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

              NOT ISNULL([WCAB/ADJ#])

              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
                Okechukwu Ossai

                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
                • WCAB/ADJ# filter: ADJ100000. Exclude all null values

                 

                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

                • 5. Re: Calculating percentages % - v10.5
                  conacher

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

                  • 6. Re: Calculating percentages % - v10.5
                    Okechukwu Ossai

                    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
                      conacher

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

                      Cannot see workbook.png

                      • 8. Re: Calculating percentages % - v10.5
                        Okechukwu Ossai

                        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
                          conacher

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

                          • 10. Re: Calculating percentages % - v10.5
                            Okechukwu Ossai

                            I've emailed it.