how to sort a formula field with having running total of a formula fie 1

[jraja]

I have report having following fields and I'm including data for 4 rows as sample

Mnth1LD Mnth1Loc Mnth2LD Mnth2loc Month3LD Month3Loc
320.83 682.39 197.00 696.46 31.89 798.85
395.11 1137.26 430.86 1152.85 16.19 1249.93
458.22 569.35 255.82 578.33 19.69 713.48
370.08 1040.50 221.89 1049.27 16.42 1150.32


3MnthAvgLD 3MntAvgLoc Variance
193.25 725.89 91%
280.82 1180.11 86 %
244.64 620.39 84%
202.80 1080.03 90%

Here what I'm doing first I wrote formula to get the value of Mnth1LD Mnth1Loc Mnth2LD Mnth2loc Month3LD Month3Loc and then I get running total to get the value of 3MnthAvgLD all ld field and divide by 3 and
running total for all 3MntAvgLoc fields and divide by 3
then I addup 3MnthAvgLD and 3MntAvgLoc divided by Mnth1LD+ Mnth1Loc
and get the value into the variable
I need to sort that variance in asc order
like 91%,90%,86%,84%
which is comming from finalString
My question is how can I sort this variable
when I can not this formula in record sort exper,
group sort expert becuase I'm using runnig total RSumofFistMonth (because I can not get the vaule by adding simply by Mnth1LD + Mnth1Loc
following is the code

Numbervar VSum3MonthAvg:={@3MonthAvgLD}+{@3MonthAvgLocal};

NumberVar RsumFirstMonth:={#RSumofFistMonth};
NumberVar VdivValue:=RsumFirstMonth /VSum3MonthAvg;
NumberVar VdivValueTr:=VdivValue*100 ;
stringVar strVdivValueTr:=totext(VdivValueTr);
NumberVar pos:= instr(strVdivValueTr,".",1);
stringVar finalString:=left(strVdivValueTr,pos-1);
finalString+'%'


Can you help me please

 

[synapsevampire]

If you use Summaries (right click the field and select insert summary) instead of Running Totals you should be able to use them in the Group Expert.

Running Totals are evaluated at print time, so they aren't available before.

-k

 

[jraja]

How can you calculate 3MnthAvgLD which is @Mnth1LD+@Mnth2LD+@Mnth3LD

 

[jraja]

how can I sort stringVar finalString

 

[dgillz]

jraja,

You cannot sort anything that uses variables. Nor do any of your formulas need variables. So why are you using them?

Please post your formulas for @Mnth1LD, @Mnth2LD and @Mnth3LD. We will rewrite them without using variables, then do as Synapse suggested.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[jraja]

Month1LD is summery of @Month1LD
formula is
dateTimeVar invDate := {avReports_Safeway_DM_03.InvoiceDate};
numberVar MnthsToSub := -1;
dateTimeVar prevMonths := DateAdd ("m", MnthsToSub , currentdate);
if (Month (invDate) = Month(prevMonths) And Year(invDate) = Year(prevMonths)) and (Right ({avReports_Safeway_DM_03.LedgerPart5Code},3 )<> "779") then
{avReports_Safeway_DM_03.LedgerAllocChgAmount}
else
0

@Month1Local is
dateTimeVar invDate := {avReports_Safeway_DM_03.InvoiceDate};
numberVar MnthsToSub := -1;
dateTimeVar prevMonths := DateAdd ("m", MnthsToSub , currentdate);
if (Month (invDate) = Month(prevMonths) And Year(invDate) = Year(prevMonths)) and (Right ({avReports_Safeway_DM_03.LedgerPart5Code},3 )= "779") then
{avReports_Safeway_DM_03.LedgerAllocChgAmount}
else
0

Month2LD
dateTimeVar invDate := {avReports_Safeway_DM_03.InvoiceDate};
numberVar MnthsToSub := -2;
dateTimeVar prevMonths := DateAdd ("m", MnthsToSub , currentdate);
if (Month (invDate) = Month(prevMonths) And Year(invDate) = Year(prevMonths)) and (Right ({avReports_Safeway_DM_03.LedgerPart5Code},3 )<> "779") then
{avReports_Safeway_DM_03.LedgerAllocChgAmount}
else
0

Month2Local

dateTimeVar invDate := {avReports_Safeway_DM_03.InvoiceDate};
numberVar MnthsToSub := -2;
dateTimeVar prevMonths := DateAdd ("m", MnthsToSub , currentdate);
if (Month (invDate) = Month(prevMonths) And Year(invDate) = Year(prevMonths)) and (Right ({avReports_Safeway_DM_03.LedgerPart5Code},3 )= "779") then
{avReports_Safeway_DM_03.LedgerAllocChgAmount}
else
0

Month2LD
dateTimeVar invDate := {avReports_Safeway_DM_03.InvoiceDate};
numberVar MnthsToSub := -3;
dateTimeVar prevMonths := DateAdd ("m", MnthsToSub , currentdate);
if (Month (invDate) = Month(prevMonths) And Year(invDate) = Year(prevMonths)) and (Right ({avReports_Safeway_DM_03.LedgerPart5Code},3 )<> "779") then
{avReports_Safeway_DM_03.LedgerAllocChgAmount}
else
0

Month3Local
dateTimeVar invDate := {avReports_Safeway_DM_03.InvoiceDate};
numberVar MnthsToSub := -3;
dateTimeVar prevMonths := DateAdd ("m", MnthsToSub , currentdate);
if (Month (invDate) = Month(prevMonths) And Year(invDate) = Year(prevMonths)) and (Right({avReports_Safeway_DM_03.LedgerPart5Code},3 )= "779") then
{avReports_Safeway_DM_03.LedgerAllocChgAmount}
else
0




first I split the field avReports_Safeway_DM_03.LedgerAllocChgAmount so I get the data for last three month and then according to 779 if it's 779 then local else it's LD
then I add all LD fields and devide by 3 to get the average of LD same I have done with Local fields and get the average
now I have to get the variance to devide month1LD+month1loca / 3monthaverageLD+3monthAveragelocal * 100
and then I neet to sort the record on base of percentage

 

[synapsevampire]

Crystal defaults to whileprintingrecords, but to demonstrate one of your formulas without variables:

if (Month ({avReports_Safeway_DM_03.InvoiceDate}) = Month(DateAdd ("m", MnthsToSub , currentdate)) And Year({avReports_Safeway_DM_03.InvoiceDate}) = Year(DateAdd ("m", MnthsToSub , currentdate))) and (Right ({avReports_Safeway_DM_03.LedgerPart5Code},3 )<> "779") then
{avReports_Safeway_DM_03.LedgerAllocChgAmount}
else
0

-k

 

[dgillz]

SV has nailed this. Just insert summaries on formulas like these and then write formulas calculating the %. (you can format the % sign, not concatenate it).

After that you can rank the results with the TopN Expert.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[lbass]

Unfortunately, in your case, I don't believe the percentages will be available for sorting by topN/group sort even if you don't use variables. If your summary was a percentage of the grand total, you could, but your summary is a percentage as it relates to another summary field. I believe that topN/group sorts only work with inserted summaries, so what you would need is to have the percentage formula at the group level returned as a hard value that you could place in the detail section and then insert a summary on it. if your summary was not so complicated, I would suggest using a SQL expression to accomplish this.

-LB

 

[seabubble]

Hi all, This is exactly what I am working on at the moment. I have my report with Business Objects, they have been helping. What I have done so far is build the main report holding the data. Then make a sub report that uses shared fields on both the main and sub report. This "copies" the values in the main report and puts them in the sub report. I have worked out the percentage on the subreport. And now I am waiting for B Objects to get back with the answer to the last bit. GROUPING BY PERCENT!
Will post when I know how.

 

[jraja]

Hi nothing works
So I worked on store procedure and now I getting calculated value for
I create formula name "New3MonthAvg"
Using simply {avReports_Safeway_DM_03.LedgerAllocChgAmount} from view
then I create summery field from it
and Next
I modified formula for first month just modified little and named as "New1MonthAvg"
Code:

dateTimeVar invDate := {avReports_Safeway_DM_03.InvoiceDate};
numberVar MnthsToSub := -1;
dateTimeVar prevMonths := DateAdd ("m", MnthsToSub , currentdate);
if (Month (invDate) = Month(prevMonths) And Year(invDate) = Year(prevMonths))then
{avReports_Safeway_DM_03.LedgerAllocChgAmount}
else
0

I took one condition """and (Right({avReports_Safeway_DM_03.LedgerPart5Code},3 )= "779")"""
off from if statement

Now I need to devide @New1MonthAvg by New3MonthAvg and then multiply it with 100 to get percentage and then make sort of it

Now it's not giving me correct values when I do division
neither its sort entire record proper
Hope you got pretty clear picture now and be able to help me better

 

[jraja]

@New1MonthAvg /New3MonthAvg * 100
is not working and I need to sort this as percentage

 

[jraja]

Is there any help
I'm still waiting for
how can we do division on summery fields

 

[Madawc]

Try using brackets, (@New1MonthAvg /New3MonthAvg) * 100?

Failing this, make an extra detail section below the section that the users will see on the final report. See what Crystal is doing with your work fields, which will highlight any errors or faults.

"Not working" isn't a very clear description. Is the formula not syntaxing? Is it returning zero? An invalid value? Is it only sometimes working?

Madawc Williams (East Anglia, UK)

 

[jraja]

Please don't give up on me I need help

 

[dgillz]

jraja,

Lets start over. Without making me read the entire thread again, please recap exactly where you stand now, including current ouput examples, and of course your desired output examples.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[jraja]

Thanks dgillz,
I have created as synapsevampire advised two formula
one is New3MonthAvg
code is
{avReports_Safeway_DM_03.LedgerAllocChgAmount}/3;
and then I create summery to get the correct value

second is NewAvg1month
code is
if (Month ({avReports_Safeway_DM_03.InvoiceDate}) = Month(DateAdd ("m", -1 , currentdate)) And Year({avReports_Safeway_DM_03.InvoiceDate}) = Year(DateAdd ("m", -1 , currentdate)))then
{avReports_Safeway_DM_03.LedgerAllocChgAmount}
else
0
and create summery to get the correct value
both summeries are giving me correct values( is there any way to get the summery of field other then summery wizard , sum function is not working)

now

All I need to do devison on two summery field like NewAvg1month/New3MonthAvg
to get the percentage
and then on the basis of percentage I can sort the record

Your help will be appreciated and will safe from being jobless

 

[dgillz]

Insert, field, object, formula field. The Dbl click on the first summary you created, then type in a % sign, then dbl click on the 2nd summary you created.

The resultant formula will look like this:

sum({NewAvg1Month},{YourGroupingField})%sum({New3MonthAvg},{YourGroupingField})

Place this formula in your group footer.

Is this what you want? I don't think you can sort on it.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[jraja]

great this working and it's and how can do I sort (Ascending) on this field . I do not see this formula in record sort expert. or is there any way to sort the record on this field

 

[dgillz]

Your formula looks like mine? One sum() divided by another? And it is in the group footer? Please confirm this for me first.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com