Skip to Content
avatar image
Former Member

Crosstab Formula Assistance - Determining Utilization

I have had this problem before some time back, and I am racking my brain to has to how I solved a similar situation - thought the community might like a challenge.

I have a sql table with sample data below, also attached. I used this table to create a CR v2016 Crosstab report and I am trying to create a formula to determine utilization. Crosstab is displayed with line_of_business/ product as my ROWS and incurred_month as COLUMNS.

Utilization is determined by calculating (ProcsTc / MemberCount) *1000 for the given month.

the same field can be used as a summary field regardless if I change the granularity of the rows/ columns.

sample of my cross tab

LOB | JAN-2017

CO | 220 (procstc, summed) / 14992 (membercount, summed) = 0.67954 utilization

MC | 48 (procstc, summed) / 602 (membercount,summed) = 0.148265 utilization

MD | 19475 (procstc, summed) / 308151 (membercount,summed) = 60.155369 utilization

TOT| 19743 (procts, summed) / 323745 (membercount, summed) = 60.98318 util

I have a cross tab that shows - the procsTC

I have a cross tab that shows - the membercount

Now desire a cross tab that shows the utilization value only with the LOB as row and MONTH as column.

I can accomplish the utilization as an embedded summary in a cross tab that contains a row for the procedures and a row for the membercount. I wish not to have all three values in a single cross tab - since I would also like to use the utilization calculation in a charts.

Guidance, much appreciated

sum_ind lob  product              incurred_month membercount procstc
------- ---- -------------------- -------------- ----------- -----------
M       CO   Exchange - Bronze    2017-01-01     1           0
M       CO   Exchange - Gold      2017-01-01     176         0
M       CO   Exchange - Silver    2017-01-01     14815       0
M       MC   HMO                  2017-01-01     602         0
M       MD   ABD                  2017-01-01     13961       0
M       MD   ABD Child            2017-01-01     2522        0
M       MD   Duals - Opt In       2017-01-01     11294       0
M       MD   Duals - Opt Out      2017-01-01     5680        0
M       MD   Medicaid Expansion   2017-01-01     90384       0
M       MD   TANF                 2017-01-01     184310      0
C       CO   Exchange - Bronze    2017-01-01     0           6
C       CO   Exchange - Gold      2017-01-01     0           5
C       CO   Exchange - Silver    2017-01-01     0           209
C       MC   HMO                  2017-01-01     0           48
C       MD   ABD                  2017-01-01     0           1608
C       MD   ABD Child            2017-01-01     0           89
C       MD   Duals - Opt In       2017-01-01     0           2069
C       MD   Duals - Opt Out      2017-01-01     0           636
C       MD   Medicaid Expansion   2017-01-01     0           7541
C       MD   TANF                 2017-01-01     0           7532


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Nov 30, 2017 at 08:48 AM

    Create 3 formulae

    @Procs
    If Sum_Ind = 'C' then MemberCount*1000 else 0

    @Members
    If Sum_Ind = 'C' then ProcsTC*1000 else 0

    @Utility
    @Procs/@Members

    Add @Utility to Crosstab with a Sum Summary

    Ian

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Crosstab will allow for the changes in date ranges (column). If there is an easier method that cross tab and not creating a manual cross tab - I am all ears.

  • Dec 05, 2017 at 09:44 AM

    Hi Roger,

    There are a couple of ways to do this:

    Using an Embedded Summary (You're also able to choose which Summary to Chart off of):

    1. Add Procstc as the first summary field. Add Member count as the second Summary field,

    2. Right-click the MemberCount summary and click 'Embedded Summary' > Insert Embedded Summary.

    3. Right-click on the cells labelled 'Edit this formula' > Embedded Summary > Edit Calculation Formula and use this code:

    GridValueAt(CurrentRowIndex, CurrentColumnIndex, 1) / GridValueAt(CurrentRowIndex, CurrentColumnIndex, 0)

    4. Right-click the crosstab and click Insert Chart.

    5. Right-click the Chart > Chart Expert > Data tab > Under 'Show' select the Utlization Embedded Summary.

    A single Summary field:

    1. Insert a Group on the LOB field.

    2. Insert a Second Group on the Month field (set it to 'Show for each Month')

    3. Suppress the Group headers and Footers

    4. Create the Utlization formula such that it uses Summaries:

    Sum({Procstc}, {Month}, "monthly") / Sum({MemberCount}, {Month}, "monthly")

    5. Place the Crosstab on the Report Header. Use the LOB field as the Row and Month as the Column (also set to show for each month)

    6. Use the Utlization formula as the Summary with it Summary function set to 'Maximum'

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded