Crosstab Formula Assistance - Determining Utilization

[Former Member]

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

[abhilash_kumar]

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

[Former Member]

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

[Former Member]

I tried this variation, and yours, the cross tab yields 0 value.

I feel this situation needs the use of GRIDVALUEAT or GRIDROWCOLUMNVALUE or similar. I have less familiarity with these formulas and can't locate good examples of their use.

And while the sum_ind just identifies the record type, I am not sure its needed in the calculations, Since "membercount" has its own column and "procstc" as well.

For the data provided, utilization will be different for each LOB/ Product - but overall the utilization total for JAN2017 should be calculated as 60.98

[Former Member]

What is your grouping? Because your member data and Proc data are in different records you need to sum at a higher group level. Try just adding summaries of these fields to CrossTab and that may indicate why you are getting zero in your utilization formula.

To get a result you must group only at Product level, adding any other level will be too granular. Why do you need to use a Cross tab?

Ian

[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.