Simple field division problem 1

[zenzabar]

I'm using Crystal X1 and a relative new user. Working off of an excel P&L statement worksheet imported to Crystal. Data looks like this -

Actual Description YTD

100 Visits 500
125.00 Gross Revenue 10,000
100.00 Net Revenue 8,000

The formula I need to create is Gross Rev/Visits that will give me the Gross Revenue per visit in dollar amount.

 

[synapsevampire]

Presumably you've grouped the report by an entity that each has it's own visits and revenue, or there is only one per report.

Grouped version:

Group header formula:
whileprintingrecords;
numbervar MyVisits := 0;
numbervar MyRev := 0;

Details formula:
whileprintingrecords;
numbervar MyVisits;
numbervar MyRev;
If {table.Description} = "Visits" then
MyVisits : = {table.Ytd}
else
If {table.Description} = "Gross Revenue" then
MyRev : = {table.Ytd}

GroupFooter formula (where you display)
whileprintingrecords;
numbervar MyVisits;
numbervar MyRev;
if MyVisits > 0 then
MyRev/MyVisits
ele
0

-k
-k

 

[zenzabar]

OK, I'm following, but not quite the result I need. What should be the result from dividing the net revenue dollar amount ($100.00) from the visit number (100) so $100/100. I will need to do this for both the actual side and for the YTD side in seperate formulas. Thanks

 

[lbass]

You changed your requirements--your first post said you wanted to divide gross revenue by visits. So just change SV's formula so that this part reads:

If {table.Description} = "Net Revenue" then
MyRev : = {table.Ytd}

Set up a second set of variables in the same way for the YTD results.

-LB

 

[zenzabar]

Thanks for the help with this formula, however I still have a small snag. I'm not getting the desired result and ending up with the formula returning the original value of visits. This is what my formula placed into the detail area looks like. I have not put any grouping in place for the dataset.

whileprintingrecords;
numbervar MyVisits;
numbervar MyRev;
If {'Clinic_Networkwo_'.DESCRIPTION}= "Visits" then
MyVisits := {'Clinic_Networkwo_'.ACTUAL}
else
if {'Clinic_Networkwo_'.DESCRIPTION} = "Net Revenue" then
MyRev := {'Clinic_Networkwo_'.ACTUAL}
else
if MyVisits > 0 then
MyRev/MyVisits
else
0

 

[lbass]

You should use SV's formulas, except you don't need the group header formula, and the group footer formula belongs in the report footer.

-LB

 

[zenzabar]

Now I have made the change to the detail area formula to be:

whileprintingrecords;
numbervar MyVisits;
numbervar MyRev;
If {'Clinic_Networkwo_'.DESCRIPTION} = "Visits" then
MyVisits:= {'Clinic_Networkwo_'.ACTUAL}
else
if {'Clinic_Networkwo_'.DESCRIPTION} = "Net Revenue" then
MyRev:= {'Clinic_Networkwo_'.ACTUAL}

Report Footer shows this formula:

whileprintingrecords;
numbervar MyVisits;
numbervar MyRev;
if MyVisits > 0 then
MyRev/MyVisits
else
0

The Visits actually is 18220; Net Rev is $1,500,000. Formula should result in 1,500,000/18220 to get $82.33 What I get though is 18220 from the detail formula and a 0 from the report footer formula. What am I missing?

 

[lbass]

On second thought, I think the detail formula should be set up like this:

whileprintingrecords;
numbervar MyVisits;
numbervar MyRev;
If {'Clinic_Networkwo_'.DESCRIPTION} = "Visits" then
MyVisits:= {'Clinic_Networkwo_'.ACTUAL};
if {'Clinic_Networkwo_'.DESCRIPTION} = "Net Revenue" then
MyRev:= {'Clinic_Networkwo_'.ACTUAL};

-LB

 

[zenzabar]

Thanks so much for continuing to work on this with me. I changed the formula in the detail area to your suggestion which makes more logical sense, but now it's returning a 0 value. It's so close!

 

[lbass]

What is returning 0? The formula display in the detail section is irrelevant and should be suppressed.

Check the value of MyVisits and MyRev in the report footer, by using formulas like:

whileprintingrecords;
numbervar MyVisits;

-LB

 

[zenzabar]

OK, I'm only looking at the formula in the report footer. Problem is with MyRev as it returns a value of 0; while Myvists does return the correct number.

 

[lbass]

Then you need to check the formula in the detail section. Does it show the correct value for the detail line that contains the description "Net Revenue"?

-LB

 

[zenzabar]

I see a problem...In the description column because this was imported from excel, the net revenue is a total and is indented one space before the number actually starts. If I go back to the original spreadsheet in excel and set the columns to all left align, bring the table back into crystal, that should take care of this issue.

 

[lbass]

Just change the formula to:

whileprintingrecords;
numbervar MyVisits;
numbervar MyRev;
If trim({'Clinic_Networkwo_'.DESCRIPTION}) = "Visits" then
MyVisits:= {'Clinic_Networkwo_'.ACTUAL};
if trim({'Clinic_Networkwo_'.DESCRIPTION}) = "Net Revenue" then
MyRev:= {'Clinic_Networkwo_'.ACTUAL};

Also make sure the case of the text is the same.

-LB

 

[zenzabar]

You have saved me another step; thanks so much for all of your assistance!