Why my formula not working!? 3

[jehanzebn]

Dear all I have created a report with number of set date ranges, daily, last 5 days,last 4 weeks,year to date etc.

The report is pulling everything correctly meaning the total number of jobs, lenses, sub total and grand total are coming correctly. However when I introduced the percentages, they don't come up correctly.

What I am trying to do is to get the report setup with YTD date range and then divided the actual report into Daily, Last 5 days, last 4 weeks and year to date.

So it looks like this

daily records - Last 5 days records - Last 4 weeks records- Year to date records

Then each has subtotals and grand totals.

I want to show each percentage, for example if the last 4 weeks record has 5 then the percentage should be from the grand total of the last 4 weeks jobs.

However that is not the case because I have setup the report records to Yeartodate and when I run the report, though the running totals are fine, the percentage is coming out of the grand total of the year to date.

I am frustrated and sick and tired of trying every thing I could and now I don't know what else to try.

Could someone please help with this.

here is my formula sequence I used for percentage for each one of the date format.

CR Jobs percentage - this is the cr percentage for the daily records

Whileprintingrecords;
Numbervar Jobs:=0;
Jobs:={#TotalCR};
Jobs%DistinctCount({foccredsum.doc_no});

FOC Jobs percentage - This is the FOC percentage for daily records

Whileprintingrecords;
Numbervar Jobs=0;
Jobs:={#TotalFOC};
Jobs%DistinctCount ({foccredsum.doc_no});

FOCR Jobs percentage - This is total percentage for daily records

Whileprintingrecords;
numbervar crd:=0;
numbervar focd:=0;
numbervar focrd:=0;
crd:={@JobsPercentageCRDaily};
focd:={@JobsPercentageFOCDaily};
focrd:=crd+focd;
totext(focrd,2)+"%"

When I set the report record criteria to Currentdate, the percentage comes up correctly for daily date range however then Last 5 days and last 4 weeks shows no data. Year to date shows the same data as current date as it lies on it.

I suspect that I have to setup something on the 1st and 2nd formula before the distinct Count but I don't know what.

I tried like this
CR Jobs percentage edited - used the date range here in this formula but this didn't work either.

Whileprintingrecords;
Numbervar Jobs:=0;
Numbervar jobcounter:= if {foccredsum.line_type}="C" and {foccredsum.date_created} in ((Minimum(last7days))-1) to((Maximum(Last7days))-1) then DistinctCount({foccredsum.doc_no});
Jobs:={#TotalJobsCRL5D};
if jobcounter=0 then 0 else
Jobs%jobcounter;

Can someone please help me. I am using Crystal Reports 2008 with Informix database via ODBC connection.

Many thanks in advance

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

Thanks Ibass, I know I am bit frustrating when asking questions and I do apologize for that.

Ok I have tried this method,

Formula for percentage daily

DistinctCount({foccredsum.doc_no},{foccredsum.reason_code})/{@DailyGrandTotalJob}*100;

Now in here the DistinctCount, is counting the numbers however when it comes to 0, it gives error. How can I stop this without using a variable in my formula?

I tried

If (DistinctCount({foccredsum.doc_no},{foccredsum.reason_code}))=0 then 0 else
DistinctCount({foccredsum.doc_no},{foccredsum.reason_code})/{@DailyGrandTotalJob}*100;

but it still gives error Cannot divide by 0.

If I try the suggestion,

({#DailyCRJob}+{#DailyFOCJob})% DistinctCount({foccredsum.doc_no},{foccredsum.reason_code});

It still gives percentage from the grand total of Year to date, instead of Daily Grand Total.

I do understand what you are saying Ibass but for some reason I cannot implement it properly in my report. I mean I did what you suggested, and did it exactly what you suggested however it doesn't seem to work.

Each distinctCount (filtered by date) should be divided by Each Grandtotal respectively (filtered by date).

Daily DistinctCount should be divided by Daily Grand Total, Last 5 days DistinctCount should be divided by Last 5 Days grand total and so on.

This is what's not happening. The suggestion you provided when I use it, it divides each "daily" or "Last 5 days" with Grand total of Year to date!.

It's so frustrating, I have tried number of methods and so far none seems to be working properly.

Regards

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

Please do the following and then replicate this for other percentage formulas. First create a formula {@null} by opening and saving a formula without entering anything.

Then create a formula like this:

//{@currdate}:
if {table.date} = currentdate then
{foccredsum.doc_no} else
tonumber({@null}) //remove the tonumber() if the doc no is a string

Then create a second formula for the percentage:

({#DailyCRJob}+{#DailyFOCJob})% DistinctCount({@currdate},{foccredsum.reason_code})

This would give you the daily percent at the reason code group level.

-LB

 

[jehanzebn]

Thanks Ibass it is working perfectly however now the problem comes with Lenses percentage.

In Lenses there are not only two types but the fields are two different ones.

For example
For FOC Lens - It's QTY field - Data Type: Number

if {foccredsum.date_created} =Currentdate then
{foccredsum.qty}
else
tonumber({@null})

Whereas For CR Lens - It's Stock field - Data Type:String

if {foccredsum.date_created} =Currentdate then
{foccredsum.stock_code}
else
{@null}

Now in the case of lenses these two fields are different. How can I accomodate both into one?

I tried this,

if {foccredsum.date_created}=Currentdate then
(Sum({foccredsum.qty})+Count({foccredsum.stock_code})
else
tonumber({@null})

But that didn't work.

I tried by creating further two separate formulas for each one and putting them into the percentage formula but that didn't work either.

Formula one:

if {foccredsum.date_created} =Currentdate then
{foccredsum.qty}
Else
tonumber({@null})

Formula 2

if {foccredsum.date_created} =Currentdate then
{foccredsum.stock_code}
else
{@null}

Percentage formula for lenses

({#L5DCRLens}+{#L5DFOCLens})%(Sum({@l5ddateqtylens})+ Count({@l5ddatestocklens}))

When I run the above, CR doesn't show any popup error however it opens the Qty formula window.

How can I get this to work? Any advise will be much appereciated.

P.S. You have done a great help to me by showing the above formula. It is working perfectly for Jobs! and I am very much thankful for your effort in this case.

Many Many thanks

Kind Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

Now it's giving error upon runtime String is non-numeric under the QTY (Data type=Number) formula.

if {foccredsum.line_type}="F" then
{foccredsum.qty}
Else
tonumber({@null})

Regards

jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

I can't follow why you would be trying to add a stock code to a quantity. Makes no sense.

There is nothing wrong with your last formula, assuming the qty is a number field.

-LB

 

[jehanzebn]

The Stock Code is a string and Qty is a number. Stock code represent CR lens whereas Qty represent FOC Lens.

Now to get a combined report I need to add both together.

That is the reason why I need to add stock code to qty.

The report produces perfect results other than Stock Code, i.e. the string.

I tried

if Line_type="C" then
{foccredsum.stock_code}
else
{@null};

However when the percentage comes up, it shows the percentage from the Year to date lens grand total instead.

When I break down each job and lens I found out that everything is working fine except CR lens, which is this stock code (string).

I don't understand why rest of them are working perfectly but not this one, even though "foccredsum.doc_no" is a string and I am using the same formula for it and it is working for all jobs. Infact the qty(number) works fine as well.


Regards

Jehanzeb

[b]Jehanzeb[/b]
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

I'm having trouble believing that stock code is really a quantity field, and not a simple ID field that should not be used in calculations.

-LB

 

[jehanzebn]

No no, Stock Code is not Qty field.

Qty field = FOC Lens field (Number)
Stock Code = CR Lens Field (String)

The Qty field represents Free of charge lenses whereas Stock Code field represents Credit Lenses.

They are two different fields.

We get two type of returns, one credit and other free of charge, what I need is to combine the two together so that users don['t have to print two reports separately

The two fields are totally different however need to combine them so that I get combined result.

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

But you are saying that the stock_code represents a quantity, correct? Why would anyone set up a quantity field as a string? Anyway, change the stock code formula to:

if Line_type="C" then
tonumber({foccredsum.stock_code})
else
tonumber({@null})

However, I don't know how you are using this, and I don't have the patience to wade through all the detail you have provided so far. I wouldn't use it inside a running total, but only for inserted summaries.

-LB

 

[jehanzebn]

IIbas, I am not saying Stock Code is equal to Qty. What I am saying is Stock code values gets added into Qty value.

They are not the same. I am not setting Qty field as a string, instead I want to set String field into number so that I can add it.

Stock Code = String field
Qty = Number field

Stock Code != Qty

However

Qty value + Stock Code value = Total Value

I am not sure how else I can explain this. however I am going to test what you have suggested now.

Regards

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

IIbas, when I used your method given above I received an error message "The field is non-numeric field".

The Stock Code (String) has values like "CR2304", "CR2343" etc

Does that make any difference to the field if we make it as number?

Regards

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

If it has values like that, why would you be trying to add it? I think you are confusing your fields. It looks to me like the stock code is an ID field, NOT a field for calculations. You need the field that corresponds to the stock code that holds the value field.

If you insist that this field does hold the value, then you would have to use something like:

tonumber(mid({table.stockcode},3))

...but I think you would be going down the wrong road.

-LB

 

[jehanzebn]

IIbass, firstly sorry for the late reply. I was trying to work it out what could be the ID, instead of using the Stock Code (String) and I did find out that Qty upon lens_type="C" is exactly the same. In other words it was the reference to the ID.

You were right there.

Additionally I could not use the formula you suggested in your last post because I found out that not all string values were CR2343 but some were 123212+121, 1-292-0283-, 123+233 etc

and you can't really convert them into numbers.

Anyhow, it is all working and I am very VERY grateful to you and I really appreciate your patience and help with this issue.

Many Many Thanks

Regards

Jehanzeb ))


Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[CR85user]

Although, you are still not quite grateful enough to award LBass a star for her perseverance as with the other 43 threads which you have posted in the formula forum and only 1 star given out despite the various users who have tried to assist.

It might be worth you reviewing your previous posts to give the likes of LBass some recognition of your gratitude for their assistance.

(Click on 'My Info' on the top left of the page for your current stats)

LBass please accept my thanks and recognition for your effort (*).

'J

CR8.5 / CRXI - Discovering the impossible

 

[jehanzebn]

Aha! Thanks CR85user, in other forums it is very clear with radio buttons to give users points like 5, 10 15 for their answers however I never knew how to give out points on this one or else I would have given him points right away. Specially the amount of help he has provided.

There is no doubt about it.

Many thanks

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

Ok I clicked on my info but what next?

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[CR85user]

Hi Jehanzeb,

After clicking on 'My Info' click on the number below 'Threads
Started' to view all of your previous subjects and see how they got resolved.

Well done on spotting it

'J

CR8.5 / CRXI - Discovering the impossible

 

[jehanzebn]

Thanks got it

from now on I will be clicking on that thanks button!

Many thanks for pointing it out to me.

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[dfor]

I'm not the greatest at this stuff, but maybe one of these will help you.
If you use:
({foccredsum.date_created}) in LastFullWeek
it will select dates from last week, Sunday to Saturday.
or
({foccredsum.date_created}) in LastFullMonth
to select all dates last month
or
{foccredsum.date_created} in dateadd("d", -7, currentdate) to currentdate
to select the last 7 days and include today
or
{foccredsum.date_created}in (dateadd("d", -7, currentdate-day(currentdate)+1)) to (dateadd("d", -1, currentdate-day(currentdate)-1))
for the last 7days excluding today