Sum or count the group if it qualifies 1

[omoralez]

There are three types of qualifing records in a case, each record can only qualify for one of the types. I need to total the cases(group) that have that type of record. So, if the case has: 2 type 1, and 3 type 2, but no type 3, it would count 1 for type 1, 1 for type 2, and 0 for type 3. I'm not sure of how to do this, I have tried grouping, but sum of the sum does not work.

My problem with a crosstab is that I have to report on all three types, even if one of the types does not have a total.



Case Type
1X015M6
1X015M6 1/26/10 2
1X015M6 1/26/10 1
This should count 1 type 1, 1 type 2
1X01NS5
1X01NS5 1/26/10 1
1X01NS5 1/26/10 1
1X01NS5 1/26/10 3
This should count 1 type 1, 1 type 3
1X03V691
1X03V691 1/29/10 2
1X03V691 1/29/10 3
This should county 1 type 2, 1 type 3
1X0BS87
1X0BS87 1/29/10 2
1X0BS87 1/29/10 2
1X0BS87 1/29/10 1
1X0BS87 1/29/10 1
1X0BS87 1/29/10 3
1X0BS87 1/29/10 3
This should county 1 type 1, 1 type 2, 1 type 3

So, the total would be 3 for type 1, 3 for type 2, 3 for type 3

 

[Turkbear]

Hi,
Create 3 formulas:

@Type1
If Type = 1 then 1 else 0

@Type2
If Type = 2 then 1 else 0

@Type3
If Type = 3 then 1 else 0

Sum those to get a count of each type.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[omoralez]

That will not work, I don't want a count of all of the records that have that type. I need a count of all cases that have at lease 1 of that type.

 

[lbass]

Change Turkbear's formulas to:

@Type1
If Type = 1 then
{table.case} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything.

Repeat for Type2 and 3 and then insert distinctcounts on each formula.

-LB

 

[Turkbear]

Hi,
I misunderstood that part.
Thanks, LB for the amendment - should work a treat.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[omoralez]

Thank you that worked.