Need to show a date for Empty Field Values 1

[MrArmstrong]

I am new to all of this, I have a report that shows items issued from our stock on certain dates. We have orders open that haven't been issued yet. On my report those fields are empty for the open orders. I want to show the last day of the month i.e. 7/31/07 for all empty fields. Is there a formula for this to show the date of the orders issued and 7/31/07 for all empty fields.

 

[lbass]

What determines what month is used?

-LB

 

[MrArmstrong]

There would be no specific month. It might be February and my boss wants to know our pentage of Items that didn't get issued in January, so I want to fill in all open fields with 1/31/07. If he wants it in March then I would want to use 2/28/07. Whatever the last day of the previous month was.

 

[lbass]

Try:

if isnull({table.date}) then
currentdate-day(currentdate)

This assumes that you always are evaluating dates in the month previous to the currentmonth.

-LB

 

[MrArmstrong]

That didn't work,O.K. mabe I didn't give all details.I am pulling sales orders for the month and I am pulling any issue tickets attached to those. so if the open sales orders don't yet have pick tickets then maybe they are considered emty instead of Null? The empty spaces on my report for the dates are the ones that have no issue ticket yet.Does this help anymore.

 

[lbass]

Do you see a row with the sales order with an "empty" date field? Or is the entire row missing?

-LB

 

[MrArmstrong]

No it pulls all Sales Order for the Month Open or Closed. So the Issue dates that are empty in the Issue date column are the open sales orders. I have a sales order number with the date the sales order was created but no date for issue because the parts haven't been issued yet. So on all of those that haven't been issued yet I show a blank date in the ticket issue date.These are the ones I want to change.

 

[lbass]

My formula should have worked if the field was actually null, so try the following instead:

if isnull({table.date}) or
{table.date} = date(0,0,0) then
currentdate-day(currentdate)

If this doesn't work, please confirm the datatype of your field. Also, if something doesn't work, you should try to explain what the result is--an error message, etc.

-LB

 

[MrArmstrong]

Sorry to keep this dog barking, but I know have another problem.The same issue documents that are missing the dates are missing document numbers. So I have all of the sales orders showing with no issue document or issue date.
I am now trying to find what percentage of sales orders do not have issue tickets against them.

When I do a count for issue tickets it counts them all.I only want it to count the actual issue tickets. What formula can i use to say exclude empty fields or count only zeros. I have all empty fields defaulting to zero.

Then I need to Know what percentage all sales orders actually have tickets created.

 

[lbass]

You could use a formula like:

if {table.field} = 0 then 0 else 1

You can substitute a formula for {table.field} if you wish. Then insert a sum (NOT a count) on this formula.

If you have any row inflation, you would use a running total with an evaluation formula:

{table.field} <> 0

-LB

 

[MrArmstrong]

o.k. YOU ARE A TRUE EXPERT AT THIS FORMULA STUU,so let me drain you for one more question.Know that I have my total number os sales orders and the number of tickets, what formula do I use to find what percentage of sales orders have tickets.I guess something like this.What percentage is (tableB)80 recoreds of (TableA) which has 100 records.

 

[lbass]

I'm unsure what your formulas are, but if you did a formula for each to eliminate the 0 values, then it would be:

sum({@tickets})%sum({@sales})

...if at the report level. At the group level, it would be:

sum({@tickets},{table.groupfield})%sum({@sales},{table.groupfield})

-LB

 

[MrArmstrong]

That was it, thank you for helping me build this whole report. Maybe knowing this details will help me in the future. I thank God for you experts.

 

[MrArmstrong]

Back Once Again, after my managers review,we have yet another question.What formula or sum/count can i use to show or count the tickets that were issued within three days of the sales orders by date.

 

[lbass]

I'm a little lost regarding what fields/formulas you are actually using. The syntax would be:

if datediff("d",{ticket.date},{sales.date}) <= 3 then 1

Then you would insert a sum on this.

-LB

 

[MrArmstrong]

Thank you any way ,but I found a formula in help. That was DateAdd("d", +3,{SO_HDR.ENTER_DATE}) , this one worked.Thank you for all you help i think you turned me into a micro Crystal Expert.