summing on maximum field

[lauriesamh]

Have CR 8.5. Still rather new at CR. Would like to do a crosstab on a group summary (maximum of field value). I would to do a distinct count of the summary field for each claimid. Is there any way to do this?

Group by: ClaimID
Summary Field = Maximum of Location (maximum string value of 0- 4)

Example
Grpheader 123456 Max of Location = 1
Groupheader 123457 Max of Location = 3
Groupheader 12348 Max of Location = 1

Crosstab should show
Location 1 = 2
Location 3 = 1

 

[lbass]

Go to the field explorer->SQL expression->new and enter the following expression {%maxlocation}:

(select max(AKA.`location`) from Table AKA where
AKA.`ClaimID` = Table.`ClaimID`)

In the expression, substitute your exact table name for "Table" wherever you find it, and substitute your exact field names for "location" and "ClaimID". Leave "AKA" as is, since it is an alias table name.

Then insert the crosstab and use {%maxlocation} as your row field and use distinctcount of {Table.claimID} as your summary field.

-LB

 

[lauriesamh]

LB - I tried this but I couldn't select the location field which is actually a formula field. Can I still do this with a formula field?

 

[lbass]

Probably not, but please share the contents of the formula and explain why you needed to use one instead of a database field.

-LB

 

[lauriesamh]

LB - I hope this helps.

//@Location Definitions
//OFFICE = 1
//Other = 0
//ER = 2
//OP on UB = 3
//IP = 4


if {Claims.ClaimType} = "H" and ({HCFASrvLine.ProcCode} in ("99201" to "99205") or
{HCFASrvLine.ProcCode} in ("99211" to "99215") or
{HCFASrvLine.ProcCode} in ("99241" to "99245") or
{HCFASrvLine.ProcCode} in ("99381" to "99387") or
{HCFASrvLine.ProcCode} in ("99391" to "99397") or
{HCFASrvLine.ProcCode} in ("99401" to "99404") or
{HCFASrvLine.ProcCode} in ("99411" to "99412") or
{HCFASrvLine.ProcCode} in ("99420" to "99429") or
{HCFASrvLine.ProcCode} in ("99431" to "99440"))
Then "1" else

If {Claims.ClaimType} = "H" and {HCFASrvLine.ProcCode} in ("99281" to "99288")
Then "2" else

If {Claims.ClaimType} = "H" and not ({HCFASrvLine.ProcCode} in ("99281" to "99228"))
Then "0" else

If {Claims.ClaimType} = "U" and (({UBSrvLine.RevCode} startswith "11")
or ({UBSrvLine.RevCode} startswith "12") or ({UBSrvLine.RevCode} startswith "18"))
Then "4" else


If {Claims.ClaimType} = "U" and (({UBSrvLine.RevCode} startswith "13")
or ({UBSrvLine.RevCode} startswith "14"))
Then "3" else


If {Claims.ClaimType} = "U" and (({UBSrvLine.RevCode} startswith "45")
or ({UBSrvLine.RevCode} startswith "46") )
Then "2"

else

If {Claims.ClaimType} = "U" and (not ({UBSrvLine.RevCode} startswith "45")
or ({UBSrvLine.RevCode} startswith "46") or ({UBSrvLine.RevCode} startswith "13")
or ({UBSrvLine.RevCode} startswith "14")or ({UBSrvLine.RevCode} startswith "11")
or ({UBSrvLine.RevCode} startswith "12") or ({UBSrvLine.RevCode} startswith "18"))
Then "0"

 

[lbass]

Do you really mean by using the maximum of location that you want the highest location number? Or do you mean the location number associated with the most recent date. (I'm guessing you really mean what you say, but we could probably proceed with a similar solution if you meant the latter).

-LB

 

[lauriesamh]

LB - I am trying to count each incident of the maximum location based on the Claim ID. I hope this makes sense.

 

[lbass]

I need to know whether by maximum you mean the highest number for location or whether you mean the most recent location according to a datefield. The maximum of your location formula would be "4" if all locations were present for a given claim ID. Is this what you are looking for by "maximum"? Or are you looking for what the location is for the record with the most recent date?

-LB

 

[lbass]

I need to know whether by maximum you mean the highest number for location or whether you mean the most recent location according to a datefield. The maximum of your location formula would be "4" if all locations were present for a given claim ID. Is this what you are looking for by "maximum"? Or are you looking for what the location is for the record with the most recent date? Once I know for sure what you mean by maximum then we can determine whether a SQL expression is possible for use in calculating the count.

-LB

 

[lauriesamh]

The maximum would be the maximum location for each claim as defined by the formula. For example

Claim 1234
detail line @location 1
detail line @location 0
detail line @location 4

Claim Group Header: Maximum of @location = 4

I would want a distinct count of the number of claims with the maximum location of 4 (and a distinct count of claims with max locations of 0 or 1 or 2 or 3.) I hope this clarifies.

 

[lbass]

Okay, then you can't use a SQL expression. You can create a faux crosstab by using a running total. Create two formulas:

//{@accum} to be placed in the group header for claim:
whileprintingrecords;
numbervar x4;
numbervar x3;
numbervar x2;
numbervar x1;
numbervar x0;

if maximum({@location},{table.claim}) = 4 then x4 := x4 + 1;
if maximum({@location},{table.claim}) = 3 then x3 := x3 + 1;
if maximum({@location},{table.claim}) = 2 then x2 := x2 + 1;
if maximum({@location},{table.claim}) = 1 then x1 := x1 + 1;
if maximum({@location},{table.claim}) = 0 then x0 := x0 + 1;

//{@display} to be placed in the report footer:
whileprintingrecords;
numbervar x4;
numbervar x3;
numbervar x2;
numbervar x1;
numbervar x0;

"Location 4" + replicatestring(" ", 8 - len(totext(x4))) + totext(x4,0,"") + chr(13) +
"Location 3" + replicatestring(" ", 8 - len(totext(x3))) + totext(x3,0,"") + chr(13) +
"Location 2" + replicatestring(" ", 8 - len(totext(x2))) + totext(x2,0,"") + chr(13) +
"Location 1" + replicatestring(" ", 8 - len(totext(x1))) + totext(x1,0,"") + chr(13) +
"Location 0" + replicatestring(" ", 8 - len(totext(x0))) + totext(x0,0,"")

Format {@display} to "Can grow} (right click->format field->common->can grow). If you change the font to a non-proportional font like Courier, the columns will align correctly. You can adjust the spacing by making the "8" larger or smaller. Of course, you could also lay this out using text boxes for your row labels and separate display formulas for each of the summary values, as in:

//{@x4display}:
whileprintingrecords;
numbervar x4;

-LB

 

[lauriesamh]

Thanks, this works beyond expectations! Any chance that this can be summed by a higher level grouping (i.e. date ranges)? I'm learning so much from everyone's assistance.

 

[lbass]

If you created a reset formula to be placed in the date group header, you could place the display formula in the date group footer and get the correct results per group. The reset formula would look like:

whileprintingrecords;
numbervar x4 := 0;
numbervar x3 := 0;
numbervar x2 := 0;
numbervar x1 := 0;
numbervar x0 := 0;

If you want both report level and group level formulas, you could use the above formulas for the date group (moved to the correct sections), and then create new variables for the grand totals, e.g., numbervar grtotx4, and add them to the {@accum} formula. You would need to also add clauses for them to accumulate, e.g.,

grtotx4 := grtotx4 + 1;

You would create a separate display formula, but with no reset formula.

-LB

 

[lauriesamh]

It looks like the formula wouldn't give a faux cross tab at the report footer for both the grouped date ranges, is this correct?

Ie, Date range 1 Date range 2
Location1 ## ##
Location2 ## ##
Location3 ## ##
Location4 ## ##
Location0 ## ##

 

[lbass]

I misunderstood. Don't use a reset formula. Instead, change {@accum} so that each clause is for a particular date range and rename your variables so they are specific to your date ranges, e.g.,:

if maximum({@location},{table.claim}) = 4 and
{table.date} in currentdate - 29 to currentdate then x4date0to30 := x4date0to30 + 1;
if maximum({@location},{table.claim}) = 4 and
{table.date} in currentdate - 59 to currentdate-30 then x4date31to60 := x4date31to60 + 1;

etc.

-LB

 

[lauriesamh]

LB - That's not quite what I need. My date groupings are based on a formula that has user parameter fields. This is the formula

//@Data Date Grouping
if {Claims.StmtFrom} in ({?StartDate} to {@Data End Date}) then "Utilization under Care Management" else
if {Claims.StmtFrom} in ({@Data Start Date} to (DateAdd("d",-1,{?StartDate}))) then "Utilization pre-Care Management" else
"Other"

 

[lbass]

That was just meant to be an example. You would do the following:

if maximum({@location},{table.claim}) = 4 and
{Claims.StmtFrom} in {?StartDate} to {@Data End Date} then x4uucm := x4uucm + 1;
if maximum({@location},{table.claim}) = 4 and
{Claims.StmtFrom} in {@Data Start Date} to (DateAdd("d",-1,{?StartDate}))then x4upcm := x4upcm + 1;
if maximum({@location},{table.claim}) = 4 and
not({Claims.StmtFrom} in [{?StartDate} to {@Data End Date}, {@Data Start Date} to (DateAdd("d",-1,{?StartDate}))]) then
x4other := x4other + 1;

etc.

-LB

 

[lauriesamh]

LB - I can't express how happy I am. It worked like a dream. Kudo's to you!!! Happy Holidays

L.