Calculating Percetages across Groups 1

[mdcson]

I have the following array:


Column 1 in $ Column 2 in $ % Col 2/Col 1
GH1 Year
GH2 Month
GH3 Patient Type
GH4 Hospital

GF4 $amount $amount x
GF3 $amount $amount x
-----------------------------------------------------------
RF Grand Tot $amount $amount x

What I am trying to do is to calculate the % of Column 2 with respect to Column 1, or Col 2/Col 1. What seems like a simple calculation just doesn't seem to work. I need to solve for x in order to have a value in my GF4 and my GF3 and then, finally, in my report footer.

 

[lbass]

You should be able to insert sums on col1 and col2 at all levels by right clicking on the detail field->insert summary. Then create formulas:

{@GF4%}:
sum({col2},{table.grp4field}) % sum({col1},{table.grp4field})

{@GF3%}:
sum({col2},{table.grp3field}) % sum({col1},{table.grp3field})

{@GT%}:
{@GF4%}:
sum({col2}) % sum({col1})

-LB

 

[mdcson]

This solution worked perfect for me. However, along similar lines, I have a related report that, at first, I thought would benefit from the same solution but it did not work.

What I have is the following:

hosp1 hosp2 hosp3
GH1-Date field
GF1 (Jan) % % %
(Feb) % % %
etc


I need to show percentages for each individual hospital that shows total payments/total charges for each month. Each hosp has a two digit code.

For example, for one of the hospitals I used the following:

if {PatientSummaryTBMonthly.Hospital Abbreviation}="CV" then
sum({PatientSummaryTBMonthly.TB Period Payments},{@ToDate}) % {PatientSummaryTBMonthly.TB Period Charges}
else 0

This gives me the error that "a summary has been specified on a non-recurring field. Details : formula name

 

[lbass]

You should be using formulas like this:

//{@CVPay}:
if {PatientSummaryTBMonthly.Hospital Abbreviation}="CV" then
{PatientSummaryTBMonthly.TB Period Payments}

//{@CVChg}:
if {PatientSummaryTBMonthly.Hospital Abbreviation}="CV" then
{PatientSummaryTBMonthly.TB Period Charges}

Then use a formula like:

if maximum({@CVChg},{@ToDate},"monthly") <> 0 then
sum({@CVPay},{@ToDate},"monthly")%maximum({@CVChg},{@ToDate},"monthly")

I used a maximum because it appeared that the Period Charges might appear only once (based on your formula), but maybe it should be a sum--depends upon how it appears in your database.

-LB

 

[mdcson]

When I used this formula:

if maximum({@CVChg},{@ToDate},"monthly") <> 0 then
sum({@CVPay},{@ToDate},"monthly")%maximum({@CVChg},{@ToDate},"monthly")

I got an error involving something not being in the group which is odd since {@ToDate} is the grouper. Then, I simply removed the "monthly" from the above formula and got the same error :
"a summary has been specified on a non-recurring field. Details : formula name

 

[lbass]

Okay, what is the content of {@ToDate}? Are you grouping on it on change of Month?

-LB

 

[mdcson]

The date fields that I have to work with take much manipulation.

{@ToDate} = cdate({@Year},{@MonthConvert},01) where

{@Year}=tonumber({tbl_Convert_Calendar_to_Fiscal.Calendar_Year})

{@MonthConvert}= tonumber({@Month})

{@Month}=if {PatientSummaryTBMonthly.Period}=1
then "10" else
if {PatientSummaryTBMonthly.Period}=2
then "11" else
if {PatientSummaryTBMonthly.Period}=3
then "12" else
if {PatientSummaryTBMonthly.Period}=4
then "1"
etc..... This converts a fiscal year period to a calendar year month.

 

[mdcson]

Group Header 1 = {@ToDate}
Group Header 2 = {PatientSummaryTBMonthly.Period} ( representing number in fiscal year)

 

[lbass]

Okay, so you don't need the monthly condition, because the date is always the first of the month. Can any of the fields be null: Period, Calendar Year, Hospital Abbreviation, TB Period Charges, TB Period Payments?

-LB

 

[mdcson]

No, none of the fields can be null.

 

[lbass]

I really think it has to be a null, and you should place the database fields on the report in an inserted detail_b section to observe what is going on. I suspect that the payment or charge fields may be null.

-LB

 

[mdcson]

In placing these fields in a detail section, I did not see any null fields, if I'm understanding correctly. Charges and Payments have some $0.00 cells but none that are null.

Also, if this helps, in my select expert, I have the following formula:

{@ToDate} >= dateadd("m",-13,currentdate-day(currentdate))+1 and
{@ToDate} <= maximum(lastfullmonth) and
{PatientSummaryTBMonthly.TB Account Status} <> "BAD DEBT"

since this is a report that will only show the last thirteen months of data. For each subsequent month, the new month will be added and the oldest will fall off.

I have two other reports like the one that we are currently discussing. The format is exactly the same, however, they did not include percentages. One was for the payments, one for the charges. In those, I would use these formulas to show dollars per million:

if {PatientSummaryTBMonthly.Hospital Abbreviation}="CV" then
({PatientSummaryTBMonthly.TB Period Payments}*(-1))/1000000
else 0

if {PatientSummaryTBMonthly.Hospital Abbreviation}="CV" then
({PatientSummaryTBMonthly.TB Period Charges})/1000000
else 0


By dividing by a whole number, I didn't have any problems. In this report I am, in essence, replacing the million with the charges to show, instead of dollars per month per hospital, the percentage of payments with respect to charges.

 

[lbass]

I guess you should break down the formula to see which element is causing the problem, so create separate formulas and test them:

maximum({@CVChg},{@ToDate})

sum({@CVPay},{@ToDate})

Also, I'm assuming that the charge field is the same throughout the ToDate group, and that you intended it to be added once to the group total (why I used Maximum)--is this correct?

-LB

 

[mdcson]

The total charges or payments, depending on the report, would be summed in the GF1 to look like:

Hosp1 Hosp2

Jan sum(charges/1M) sum(charges/1M)
Feb sum(charges/1M) sum(charges/1M)
etc

In the case of the report we are discussing, however, I will have

Hosp1 Hosp2

Jan charges/payments charges/payments
Feb charges/payments charges/payments
etc

Not sure if I am answering your Question, however, I wouldn't think to use Maximum because I need the sum total of all those charges, payments, etc to show up for each month.

Now that I think of it, I am wondering if this could be a grouping issue (just throwing something out there) as I have Period as a secondary grouper but am adding on GF1 instead of GF2.

 

[lbass]

No, this wasn't what I was looking for.

Place the formulas I mentioned in the todate group section and see if you get an error message for one of them.

If you place the charge field in the detail section, does it have the same value for every record in the todate group?

-LB

 

[mdcson]

LBass, thank you for all of your help. I was finally able to get this to work. Per your suggestions, I did the following:

{@CVPay}:
if {PatientSummaryTBMonthly.Hospital Abbreviation}="CV" then
{PatientSummaryTBMonthly.TB Period Payments}

{@CVChg}:
if {PatientSummaryTBMonthly.Hospital Abbreviation}="CV" then
{PatientSummaryTBMonthly.TB Period Charges}

Then, what worked for me was to make everything a sum. Maximum didn't work in this case. I did the following:

if sum({@CVChg},{@ToDate}) <> 0 then
sum({@CVPay},{@ToDate})%sum({@CVChg},{@ToDate})

 

[lbass]

Glad you got it working.

-LB