Selecting Summary Values for Narrative 1

[shelby55]

Hello

I'm using Crystal 9.0 and accessing data from an Access 2002 database.

I have created a graph that can be run by quarters or by month. Both the quarter and month fields are text i.e.
qtrname=If Month({NACRS.AdmDateTime}) in [4,5,6] then "Q1-"+{@Fyear2} else
If Month({NACRS.AdmDateTime}) in [7,8,9] then "Q2-"+{@Fyear2} else
If Month({NACRS.AdmDateTime}) in [10,11,12] then "Q3-"+{@Fyear2} else
If Month({NACRS.AdmDateTime}) in [1,2,3] then "Q4-"+{@Fyear2}

month=ToText({NACRS.AdmDateTime},"MMM-yyyy")

What I want to be able to do is create a line of narrative based on the results but how do I reference minimum and maximum values? For instance, in my whileprintingrecords statement I indicate

if maxmonth=maximum(Month) then sum(cases,elementA) but that only selects the first summary value. Crystal won't let me select maximum(sum(cases,elementA)).

My second problem is that it's not recognizing QtrName in sequence i.e. if I have Q4-2008/09 it thinks that is higher than Q2-09/10. I'm thinking I'll need to create two fields - one for use in the graph (where I select specified sort so that the above issues with text don't occur) and one for the narrative but I still don't know what to do.

All assistance greatly appreciated.

 

[lbass]

What are you trying to achieve by identifying your minimum/maximum summaries? Where is the narrative located? You could create a variable that would allow you to identify minimum and maximum summaries if you displayed the results in the report footer. If you are interested in this approach, let me know.

Regarding your Qtr Name sequence, you could reverse the order of your concatenation, showing the FY first and the Qtr second. Then change the display of this formula in the customize group name formula area.

-LB

 

[shelby55]

Hi LB

Thanks for the quick response!

The graph is working fine (showing ERLOS and ER 90th perc LOS) but I want to create a line of narrative comparing the first value in my range with the last.

ElementA ERLOS ER 90th perc # cases # target
Q1-08/09 3.2 5.9 5158 4985
Q2-08/09 3.3 6.1 5128 4196
Q3-08/09 3.6 6.6 4844 4599
Q4-08/09 3.6 6.7 5106 4858
Q1-09/10 3.7 6.6 5211 4946
Q2-09/10 4.0 7.2 4983 4645

My "ElementA" is just using a boolean to determine if the graph indicator is quarter versus month so the above is summary data. The cases is the number of cases used in the ER LOS formula and # target are those that met the target of 8 hours so both of these figures would be used to create my % meeting target indicator in the narrative.

So I would need the min/max values to use in the narrative like:

"In Q1-2008/09 the ER LOS was 3.2 hours compared to 4.0 hours in Q2-09/10" and also

"In Q1-2008/09 9/10 people waited up to 5.9 hours in the ER compared to 7.2 hours in Q2-09/10"

So what I'm having trouble with is accessing the min/max values to include in this narrative. I also want to be able to include data elements not being shown in the graph such as % cases meeting target.

Thanks.

 

[shelby55]

Hi

Sorry but I didn't answer all of your questions: the narrative is included in the same area as the graph which is report header. But it could be report footer too so yes I would be interested in that method.

Thanks.

 

[lbass]

Create a formula like this for the ElementA group section:

whileprintingrecords;
stringvar elAminelos;
stringvar elAmaxelos;
stringvar elAminer90;
stringvar elAmaxer90;
numbervar minelos;
numbervar maxelos;
numbervar miner90;
numbervar maxer90;
if groupnumber = 1 then (
minelos := {@ELOSsummary};
elAminelos := {@ElementA};
miner90 := {@er90summary};
elAminer90 := {@ElementA}
);
if groupnumber > 1 then (
if {@ELOSsummary} < minelos then (
minelos := {@ELOSsummary};
elAminelos := {@ElementA}
);
if {@er90summary} < miner90 then (
miner90 := {@er90summary};
elAminer90 := {@ElementA}
);
if {@ELOSsummary} > maxelos then (
maxelos := {@ELOSsummary};
elAmaxelos := {@ElementA}
);
if {@er90summary} > maxer90 then (
maxer90 := {@er90summary};
elAmaxer90 := {@ElementA}
)
);

Then you should be able to reference these variables in your narrative in the report footer:

whileprintingrecords;
stringvar elAminelos;
stringvar elAmaxelos;
numbervar minelos;
numbervar maxelos;
"In "+ elAminelos +" the ER LOS was "+totext(minelos,1)+" hours compared to " + totext(maxelos,1)+ " hours in "+ elAmaxelos

Repeat for the percentile formula.

-LB

 

[shelby55]

HI LB

Thanks very much for this - it worked!!

Except now I have a new wrinkle: instead of having two metrics on the graph (and in the narrative) I have one metric (avg ERLOS) and 2 "on change of" for the graph which is data element A and fiscal year. I changed the Month field to just be ToText({NACRS.AdmDateTime},"MMM") and quarter also only includes the quarter and not the year.

So I added Fyear as a group but how do I now use that in the formula you provided to me? Thanks.

 

[lbass]

Which field is group #1 and which is Group #2? Are you still looking for grand total results? If you are looking for the max and min per inner group, move the formula there, and just change your summary formulas within the formula to include the inner group condition.
For the string variables, I would just concatenate the Element A and FY formulas.

-LB

 

[shelby55]

Hi LB

I'll do as suggested (or at least try) but to answer your question: fyear is group #1 and elementA is group#2.

 

[shelby55]

Hi LB

Strangely this isn't working correctly. I did as you suggested and actually I created an "elementB" to use in the graph so the ElementA would remain as is for use with this formula.

When running Apr 2008 to Sep 2009, the statement is picking up April 2008 as the minimum timeframe but using August 2009 as the maximum timeframe when it should be September.

The actual text of the formula is:

whileprintingrecords;
stringvar elAminelos;
stringvar elAmaxelos;
stringvar elAminer90;
stringvar elAmaxer90;
numbervar minelos;
numbervar maxelos;
numbervar miner90;
numbervar maxer90;
if groupnumber = 1 then (
minelos := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAminelos := {@ElementA};
miner90 := PthPercentile (90, {@High_Acu_LOS}, {@ElementA});
elAminer90 := {@ElementA}
);
if groupnumber > 1 then (
if WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA}) < minelos then (
minelos := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAminelos := {@ElementA}
);
if PthPercentile (90, {@High_Acu_LOS}, {@ElementA}) < miner90 then (
miner90 := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAminer90 := {@ElementA}
);
if WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA}) > maxelos then (
maxelos := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAmaxelos := {@ElementA}
);
if WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA}) > maxer90 then (
maxer90 := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAmaxer90 := {@ElementA}
)
);

Can you help with this? Thanks.

 

[lbass]

In your last clause, you are mistakenly comparing the weighted average with the maxer90. Could that be the issue?
If not, please show the values in the element group section, and then show the results you are getting.

-LB

 

[shelby55]

Hi LB

Thanks for catching that, however it's still not working. I re-copied your original formula into my report and then replaced your fields with mine so it now reads:

whileprintingrecords;
stringvar elAminelos;
stringvar elAmaxelos;
stringvar elAminer90;
stringvar elAmaxer90;
numbervar minelos;
numbervar maxelos;
numbervar miner90;
numbervar maxer90;
if groupnumber = 1 then (
minelos := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAminelos := {@ElementA};
miner90 := PthPercentile (90, {@High_Acu_LOS}, {@ElementA});
elAminer90 := {@ElementA}
);
if groupnumber > 1 then (
if WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA}) < minelos then (
minelos := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAminelos := {@ElementA}
);
if PthPercentile (90, {@High_Acu_LOS}, {@ElementA}) < miner90 then (
miner90 := PthPercentile (90, {@High_Acu_LOS}, {@ElementA});
elAminer90 := {@ElementA}
);
if WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA}) > maxelos then (
maxelos := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAmaxelos := {@ElementA}
);
if PthPercentile (90, {@High_Acu_LOS}, {@ElementA}) > maxer90 then (
maxer90 := PthPercentile (90, {@High_Acu_LOS}, {@ElementA});
elAmaxer90 := {@ElementA}
)
);

However, it is still not working in that the second formula is correctly selecting Sept 2008 (in a report showing Sep 2008 to Sep 2009)as the minimum value but incorrectly showing Aug 2009 as the maximum value.

ElementA ELOS 90thPerc formula1
Sep 2008 3.3 5.9 blank
Oct 2008 3.5 6.5 Oct 2008
Nov 2008 3.6 6.5 blank
Dec 2008 3.7 6.8 Dec 2008
.
.
Aug 2009 4.1 7.4 Aug 2009
Sep 2009 4.0 7.2 blank

Formula 2 for the narrative is:

whileprintingrecords;
stringvar elAminelos;
stringvar elAmaxelos;
numbervar minelos;
numbervar maxelos;
"In "+ elAminelos +" the ER LOS was "+totext(minelos,1)+" hours compared to " + totext(maxelos,1)+ " hours in "+ elAmaxelos

The result is "In Sep 2008 the ER LOS was 3.3 hours compared to 4.1 hours in Aug 2009." but should show Sep 2009 and 4.0 hours as the maximum.

Thanks.

 

[lbass]

Are you saying you are looking for the maximum DATE not the maximum value of the summary????

-LB

 

[shelby55]

Hi

I'm looking for the ER LOS value that corresponds with the minimum date in the range so in this case September 2008 and the ER LOS value for the maximum date/quarter in the range which is Sep 2009.

 

[shelby55]

Hi LB

After realizing what you were trying to achieve and what I wanted I just changed the formula to read:

whileprintingrecords;
shared stringvar elAminelos;
shared stringvar elAmaxelos;
shared stringvar elAminer90;
shared stringvar elAmaxer90;
shared numbervar min_erlosC;
shared numbervar max_erlosC;
shared numbervar min_er90C;
shared numbervar max_er90C;


if groupnumber = 1 then (
min_erlosC := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAminelos := {@ElementA};
min_er90C := PthPercentile (90, {@High_Acu_LOS}, {@ElementA});
elAminer90 := {@ElementA}
);

if groupnumber = distinctcount({@ElementA}) then (
max_erlosC := WeightedAverage ({@High_Acu_LOS}, {@High_Acu_denom}, {@ElementA});
elAmaxelos := {@ElementA};
max_er90C := PthPercentile (90, {@High_Acu_LOS}, {@ElementA});
elAmaxer90 := {@ElementA}
);

Now the formula works as I require it so thanks very much!!

 

[lbass]

I think you are, so this is much simpler. Do you have a parameter(s) you are using to select the date range?
And do you have a parameter to determine whether Element A is Month or Quarter?

-LB

 

[lbass]

Okay, I see you've got it. The reason I went in the direction I did was because you were referencing "values" and the min and max of the summaries also happened to be the min and max of the intervals in your sample data.

-LB

 

[shelby55]

HI LB

No reason to explain why you were trying to help - really appreciate it!! And I'm sure I'll need the formulae you gave me for future use!!

To answer your question, yes I do have a parameter selecting date range and also a boolean parameter to select Months or Quarters which is built into ElementA.

Thanks again for all your help!!