2 Replies Latest reply on Mar 1, 2013 6:09 AM by Bob Laverty

    Show cases with condition A as a percentage of total cases

    Bob Laverty

      I have a spreadsheet with counts of records for condition A and for condition B ( a Y/N indicator).  Counts are aggregated by date, county and program.  Actual counts are in the millions, so I wanted to summarize in the database rather than pull all of the individual records. 

       

      Now I want to line graph the condition A records ('Y') as percentages of total records (conditions A and B combined) by county (rows) and dates (columns), different color lines by program.  I tried different calcuated fields and table calculations and other people's examples, but can't seem to come up with the answer.  As the day draws to a close, I'm thinking less and less clearly about this.  I can read up on my own, but I can't seem to find the right key words to use.  Suggestions?

       

      Bob Laverty

        • 1. Re: Show cases with condition A as a percentage of total cases
          Joshua Milligan

          Bob,

           

          See if the attached workbook gets you going.  I ran out of time to write up any kind of documentation, but I'd be happy to answer any questions you might have!

           

          Some quick notes:

          1. I used a quick table calculation (% of Total, Computed using Pane Down) to get the percent of total for each Row at each date.

          2. I used a table calculation for the filter.  Table calculation filters are performed after the initial query, so the total (and therefore the percent of total) is truly the entire total and not a total based on filtered data.

           

          Hope it helps!  Let me know if you have any questions.

           

          Regards,

          Joshua

          • 2. Re: Show cases with condition A as a percentage of total cases
            Bob Laverty

            Thanks for the quick reply.  I didn't dig into your example yet, but I found what I needed through some more fiddling with table calculation options.  The answer was to compute using cell and editing the formula to use the full value in the denominator.

             

            Here is the description and formula:

            Totals summarize values for each County, Program, Week Of.

            SUM([ER Claims]) / TOTAL(SUM([Claims]))

             

            ER Claims is a calculated field representing the value of Claims with condition A.  The default formula that appeared for my table calculation was SUM([ER Claims]) / TOTAL(SUM([ER Claims])).  I edited the denominator and got the result I wanted.  Thanks for the help.