Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Need help on the formula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Elisha83
Groupie
Groupie


Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
Quote Elisha83 Replybullet Topic: Need help on the formula
    Posted: 15 May 2008 at 3:03am
Lot No   Date   Area Jan Feb Mac Apr May  Jun July Total TO       Avg TO
1-0-1   OCT-07 1917  800  0    900  0    1000  0     0      2700             0.47
 
 
Avg To Formula = (Total PO/ Mths with sales figure/ area)
                       = 2700 / 3/ 1917
                       0.47
 
 
Hi there~
 
I need help on how to write the above "Avg To" formula in crystal report. I dont know how to get the counting of months with sales figures only. Can anyone help me on this?
 
 
Thanks in advance~ 
3Lish@
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 15 May 2008 at 6:05am
Well, you aren't going to be able to do this with just the Average summary function.  You're going to need to use a couple formulas with global variables to pull it off.

I'm not sure exactly what your raw data looks like.  That is going to be important for determining how to structure the formulas.  I'm going to offer up two scenarios.

If your raw data looks like the line you posted there, then you can simply create one formula to do the work.  It would look something like:

Local NumberVar CountPO  := 0

IF {MyReport.Jan} > 0 THEN CountPO = CountPO + 1;
IF {MyReport.Feb} > 0 THEN CountPO = CountPO + 1;
IF {MyReport.Mac} > 0 THEN CountPO = CountPO + 1;
IF {MyReport.Apr} > 0 THEN CountPO = CountPO + 1;
IF {MyReport.May} > 0 THEN CountPO = CountPO + 1;
IF {MyReport.Jun} > 0 THEN CountPO = CountPO + 1;
IF {MyReport.July} > 0 THEN CountPO = CountPO + 1;

IF CountPO > 0 AND {MyReport.Area} > 0 THEN
    ({MyReport.Total PO}/CountPO)/{MyReport.Area}
ELSE
    0


If your raw data is more normalized, and looks like:

LotNo   Date       Area       Month   PO
1-0-1   OCT-07   1917      Jan      800
1-0-1   OCT-07   1917      Feb      0
1-0-1   OCT-07   1917      Mac      900
1-0-1   OCT-07   1917      Apr      0
1-0-1   OCT-07   1917      May      1000
1-0-1   OCT-07   1917      Jun      0
1-0-1   OCT-07   1917      July      0


Then you would need two formulas.  In the details section, create a formula that looks like:

Global NumberVar CountPO

IF {MyReport.PO} > 0 THEN CountPO = CountPO + 1


In the footer section (either group footer or report footer, depending on your report), put this formula:

Global NumberVar CountPO

IF CountPO > 0 AND {MyReport.Area} > 0 THEN
    (SUM({MyReport.PO})/CountPO)/{MyReport.Area}
ELSE
    0

(If you are breaking this down by group, then you would need to use SUM({MyReport.PO},{MyReport.MyGroup}) instead.)


If neither of those solutions fits your needs, please feel free to clarify what your data looks like.


IP IP Logged
Elisha83
Groupie
Groupie


Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
Quote Elisha83 Replybullet Posted: 15 May 2008 at 8:50pm

Hi Lugh,

I have try test the formula you gave but when I check the error, it show an error message saying that "The remaining text does not appear to be part of the formula". Then I try put like below :

formula=Local NumberVar CountTO:= 0
IF {Feedfile_for_Monthly_TO_TXT.MONTH1} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH2} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH3} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH4} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH5} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH6} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH7} > 0 THEN CountTO = CountTO + 1;
IF CountTO > 0 AND {New_Ranking_AREA_TXT.AREA  } > 0 THEN ((
{@Total Turnover}/CountTO)/{New_Ranking_AREA_TXT.AREA  }) ELSE 0
 
Then it gives me another error message saying that "A number, currency amount, boolean, date, time,date-time, or string is expected here." . So, where it has gone wrong?What should I do?
3Lish@
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 16 May 2008 at 4:53am
Well, first of all, you don't want the "formula=" bit at the top of your formula.  Are you using Crystal syntax or Basic syntax?  If you are using Crystal syntax, you don't need the "formula=" bit at all.  If you are using Basic syntax, then "formula=" should come immediately before whatever value you want the formula to return.  In your case, it should look more like:

Local NumberVar CountTO:= 0
IF {Feedfile_for_Monthly_TO_TXT.MONTH1} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH2} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH3} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH4} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH5} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH6} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH7} > 0 THEN CountTO = CountTO + 1;
IF CountTO > 0 AND {New_Ranking_AREA_TXT.AREA  } > 0 THEN
    formula=((
{@Total Turnover}/CountTO)/{New_Ranking_AREA_TXT.AREA  })
ELSE
    formula=0


See if that fixes your problem.

IP IP Logged
Elisha83
Groupie
Groupie


Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
Quote Elisha83 Replybullet Posted: 20 May 2008 at 1:55am
Hi Lugh,
 
I test the coding already but it seems still giving me an error message like below. It highlights the part of formula which i mark it using blue colour. Below is the error message.
 
Local NumberVar CountTO:= 0
IF {Feedfile_for_Monthly_TO_TXT.MONTH1} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH2} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH3} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH4} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH5} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH6} > 0 THEN CountTO = CountTO + 1;
IF {Feedfile_for_Monthly_TO_TXT.MONTH7} > 0 THEN CountTO = CountTO + 1;
IF CountTO > 0 AND {New_Ranking_AREA_TXT.AREA  } > 0 THEN
    formula=((
{@Total Turnover}/CountTO)/{New_Ranking_AREA_TXT.AREA  })
ELSE
    formula=0
 
 
Error Msg: The remaining text does not appear to be part of the formula.
 
 
Any solutions for this problem again? Sorry for the troublesome and thanks a lot~
3Lish@
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 20 May 2008 at 5:32am
Wow.  If this is the solution, I'm going to feel sheepish.

The variable declaration should also have a semicolon at the end.  I.e., your first line should be:
Local NumberVar CountTO := 0;

I'm always forgetting that damn semicolon...


IP IP Logged
Elisha83
Groupie
Groupie


Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
Quote Elisha83 Replybullet Posted: 20 May 2008 at 9:29am
Lugh, you don't need to feel sheepish as it is not the missing semicolon that causes the error. Eventhough I put in like the way that you show, it still appear the same error message.
 
Anymore idea for the solution? Thanks for all your efforts on helping~ Smile 
3Lish@
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 21 May 2008 at 5:36am
This shows that I'm typing without thinking.  I pretty much only use Crystal syntax, so I'm forgetting to translate to Basic syntax.  Variable declaration is different in Basic.

Dim CountTO as Number
CountTO = 0

Then, you don't need any semicolons in the rest of the code.  Basic doesn't use them.

Let's see if that works...


IP IP Logged
Elisha83
Groupie
Groupie


Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
Quote Elisha83 Replybullet Posted: 21 May 2008 at 10:13am
Lugh, thanks a lot for your help~ Finally I manage to get the output that I want. Really appreciate your help.
 
Anyway, I have a question to ask. How can I differentiate which formula coding is Crystal Syntax and Basic syntax? What are the differents and usage?Often heard people say but don't understand on it. LOL
3Lish@
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 21 May 2008 at 11:05am
If you look in the upper right of the Formula Expert, just above the area where you type all the text, you should see a drop-down box that allows you to switch your syntax.  You can set the default syntax in the Options dialog from the File menu.

Neither syntax is particularly better than the other.  Mostly it's a matter of familiarity.  Oddly enough, I avoid Basic syntax because it's just enough different from Visual Basic to throw me, especially if I'm moving back and forth between them.  There are a few functions that are easier in one syntax or the other.  For instance, I find that Crystal syntax is better for handling range quantities and arrays, while Basic is often easier for complex conditional structures.  IMHO, of course.


IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.035 seconds.