Crystal Reports Margin Formal Date Range

I have this formula

if {Command.extprice} = 0
then 0
else {@Margin}/{Command.extprice}

but I need to add the following requirement...

{Command.invdte} in yeartodate
jasonspopmaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oleggoldCommented:
if {Command.extprice} = 0
then 0
elsif
mmand.invdte} in yeartodate
then 1
else {@Margin}/{Command.extprice}
0
mlmccCommented:
Try this

if {Command.extprice} = 0  AND {Command.invdte} IN yeartodate then 
     0
else 
    {@Margin}/{Command.extprice}

Open in new window


mlmcc
0
jasonspopmaAuthor Commented:
I don't think I explained this well.  I need "{Command.invdte} in yeartodate" to set the date range for the data behind "{@Margin}/{Command.extprice}"

I tried the above to options but they did not give me the correct data.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mlmccCommented:
That will get you the data you need for the calculations.

If you need the other information on related records perhaps a subreport could produce it.

mlmcc
0
James0628Commented:
I believe mlmcc got the date test backwards.  Try this:

if {Command.extprice} = 0  and not ({Command.invdte} in yeartodate)
then 0
else {@Margin}/{Command.extprice}

Open in new window



 If that doesn't give you what you want, you may need to explain further, starting with what's in the @Margin formula, and what you're trying to do with this formula.

 James
0
mlmccCommented:
Don't change your formula

Use the SELECT EXPERT
CLick REPORT --> SELCTION FORMULA --> RECORD
({Command.invdte} in yeartodate)


If you are trying to get data only from one in the range then it should be

if {Command.extprice} = 0  OR not ({Command.invdte} in yeartodate)
then 0
else {@Margin}/{Command.extprice} 

Open in new window


mlmcc
0
jasonspopmaAuthor Commented:
I need the date range to be in the formula because I need to duplicate this for Monthtodate and lastfullmonth.

The issue I am having with both options listed above is that some invoices still have a zero and amount in the time ranges creating a calculation error.

How do you write (and or)
0
mlmccCommented:
WHat was wrong with your formula?

I guess we need to know when the formula should calculate the average.

mlmcc
0
jasonspopmaAuthor Commented:
My formula works fine for all data but it does not allow me to filter it by...

monthtodate
lastfullmonth
yeartodate
0
mlmccCommented:
WHat was wrong with my first formula?

if {Command.extprice} = 0  AND {Command.invdte} IN yeartodate then 
     0
else 
    {@Margin}/{Command.extprice} 

Open in new window

0
jasonspopmaAuthor Commented:
mlmcc - I get the error "Division by Zero" and it won't let me save the formula.
0
mlmccCommented:
Try

if {Command.extprice} = 0  then 
     0
else if {Command.invdte} IN yeartodate  then
    {@Margin}/{Command.extprice}  
Else
    0

Open in new window



mlmcc
0
James0628Commented:
> The issue I am having with both options listed above is that some
 > invoices still have a zero and amount in the time ranges creating
 > a calculation error.

 What do you mean by "some invoices still have a zero and amount in the time ranges"?
 "zero _and_ amount" seems contradictory.

 What do you mean by "calculation error"?
 Do you mean the divide by 0 error that you mentioned later, or just that the result is incorrect?


 I don't know which versions of the formula you've tried, but I just realized that the ones that use AND, including the first one that I posted, are wrong.  That should be OR, as in one of mlmcc's posts.

if {Command.extprice} = 0 OR not ({Command.invdte} in yeartodate)
then 0
else {@Margin}/{Command.extprice}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
No the tests are not combined into a single test but rather sequential

If the field is 0 then return 0 -- avoid the 0 divide
else If the date is in the desired range calculate the value
else return 0 -- don't want the data

That should be the same as the AND version but he says it is missing records
The OR can get 0 divisors if it is 0 and in range

mlmcc
0
James0628Commented:
No, it's the AND version that could divide by 0.  It does not do the division if extprice is 0 _and_ the date test is true.  If the date test fails, then it does the division, regardless of what's in extprice.

 The OR version never does the division if extprice is 0.  It _also_ does not do the division if the date test is not true.

 James
0
mlmccCommented:
You're right.  MY logic wasn't working last night

mlmcc
0
jasonspopmaAuthor Commented:
.
0
jasonspopmaAuthor Commented:
..
0
jasonspopmaAuthor Commented:
ok
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.