help needed for formula 3

[SeeBee]

I have job numbers, some involving different 'tasks'. I am wanting to pick out only those job numbers with just task '13' associated. Some could have other tasks than just 13 (i.e. 41,10,12,13)-but I don't want these job number's.
Can someone help me with a formula for this? If I used the Select Expert and picked 13, I would still get this job number returned in the results, as there is a 13 within. Thanks in advance.

SeeBee

 

[dgillz]

Is this a one-to-many relationship, one job, many tasks, and you only want jobs that include a task 13? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[SeeBee]

yes, that is correct.

 

[dgillz]

Create a formula field to test for task 13 as follows:

If {task}=13 the 1 else 0

Group by Job and subtotal this formula field. Then create a Group selection formula:

sum({@formField},{job})>0

this will exclude all jobs that do not include at least one task 13 Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Ngolem]

so you just want job number records where Task 13 is the ONLY task...

try this...

group by Job number

then goto Edit Selection Formula | GROUP in the main menu

REMEMBER it is the GROUP formula you want....not the record formula.

put this formula in there

DistinctCount ({Table.Task}, {Table.JobNumber}) = 1 and
{Table.Task} = 13

that should do the trick Jim Broadbent

 

[SeeBee]

I now have the report Grouped by Job Number. Am I going to insert a new formula? and where do place it - in the group footer? Thanks

 

[dgillz]

No formula is placed ON the report in this case. This is a Group selection formula. Click on report, edit selection formula, group and enter the formula as I outlined Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[SeeBee]

Here is the formula I entered: DistinctCount ({Job Detail (History).Operation},{Job Detail (History).JobNo}) =1 and
{Job Detail (History).Operation}=13

When I refreshed (re-ran)the report, zero records were returned. I know, in fact, that at least two job numbers should have been returned in the results.

(I can see each individual job number listed in the 'tree'.)Did I enter something wrong? Thanks.

 

[dgillz]

SeeBee-

Where did you enter the formula? Is this a group selection formula or a record selection formula?

I am confused by your post because the first condition is a group condition, and the second appears to be a record condition. These should not be in the same selection formula.

Also, distinct count does not belong in this scenario as I understand it. Try my formulas, one if-then and one group selection formula, and let me know your results. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[SeeBee]

I must apologize - I was using the wrong field. Here is the correction I put in for the Group selection formula:
DistinctCount ({Job Detail (History).Cost Center},{Job Detail (History).JobNo})=1 and
{Job Detail (History).Cost Center}=13
I then re-ran the report and did get some results. I have just job numbers with Cost Center 13 showing. But when I went back into my file data, to verify a few of the results, I noticed that the job number also contained cost centers 11 & 12. Again, this would be a job number that I don't want to see - only those that have a cost center of 13.
Thanks for all you help.

 

[dgillz]

SeeBee-

I am reposting my first post because you are not reading it for some reason. Here it is:

Create a formula field to test for task 13 as follows:
If {task}=13 the 1 else 0

Group by Job and subtotal this formula field. Then create a Group selection formula:

sum({@FormulaField},{job})>0

this will exclude all jobs that do not include at least one task 13

Please try the above nad let me know what happens. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[SeeBee]

I have one more little 'glitch' that we need to get around. Below is one of the results that I am seeing after the if-then-else formula.

143009
143009 13 1 390
143009 13 1 395
2
Even though I have 13 = 1 and everything else zero, when I do the sum formula I could end up with a sum of greater than 1. Can we solve this somehow, too?

Again, thanks.

 

[SeeBee]

I moved ahead, following your first post and now I have a report that lists all the jobs with at least one cost center = to 13

Still need to get around the problem I mentioned above.

Thanks.

 

[dgillz]

SeeBee-

I do not understand. You said you wanted all items with 13 included in it. Job 143009 has 13 in it and it is on your report. What is wrong with that?

Do you want only jobs where there is one and only one 13? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[SeeBee]

The job number I mentioned above is good - I want to see this because this job only contains cost center 13, it makes no difference how many 13's there are.
These two job number's below were returned as well, but I don't want these included. If I would put >=1 in the formula these two jobs would still be returned in the list.

Do I need to still use your post with 'DistinctCount'?

154922
154922 13 1 320
154922 13 1 320
154922 13 1 320
154922 13 1 320
154922 13 1 320
154922 22 0 501
5
==========================
154980
154980 11 0 114
154980 13 1 390
154980 18 0 610
154980 22 0 506
1
==========================

 

[dgillz]

So are you saying to exclude the job if it includes any value other than 13?

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

I think so, dgillz.

Perhaps this might help, IF you don't have decimals, the sum of the number divided by the count of the numbers will always return 13 if they're all 13.

You'll evaluate this at the group level.

It's a cheat, but it works...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

OK I have been re-reading the posts trying to make sense of it, and I think I got it now. You need two separate selection formulas, one record selection and one group selection.

Under report, edit selection formula, record put:
Job Detail (History).Operation}=13

Under Report, edit selection formula, group, put:
sum({@FormulaField},{job})>0

The @FormulaField is still the same as my first post:
If {task}=13 the 1 else 0

If you do all this, the report will return all items with only 13s in the {Job Detail (History).Operation} field, AND make sure there are no solo 13s, there must be at least 2 of them.

Try this and let me know.




Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

I thought that they wanted all records for each group which are 13's (whether it's qty 1 or more), but none of the records if any in the group are not 13.

Won't your record selection criteria of = 13 not allow you to evaluate if there are non-13's?

My cheat was a bit vague I guess, but it'd be cute and quick...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[SeeBee]

dgillz -

Still does not work correctly. The two job numbers I mentioned above (154922 & 154980) were still returned, it's just now showing only 13. Also, there could be only one 13 in a job number.

Some jobs have more than one cost center (10,42,12,13) and some jobs only will have cost center 13. These are the only jobs I want. If the job has other cost centers, I don't want to see the job number at all. Does this help?