Conditional Count ??

[Kim296]

Group 1 header: {lwmain.caseid},{lwmain.reportdate},{lwmain.casedescription}
Details Section: {lwcase.status},{lwcase.statusdate}

I would like to conditionally count {lwmain.caseid} based on criteria from {lwcase.status}.

For example: There is only 1 Case Number {lwmain.caseid}; however, it can have mulitple date and status changes (lwcase.status},{lwcase.statusdate}
(i.e. Active, date, Inactive, date, Warrant Issued, date, Cleared by Arrest, date, Not Enough Evidence, date, etc..)

I would like to assign each Case Number {lwmain.caseid} either a 0 or 1, based on my criteria.

If {lwcase.status} has both a Warrant Issued and Cleared by Arrest under the same case number {lwmain.caseid} then 0 else 1

I can't seem to come up with a solution that works. Any help is appreciated.

 

[pmax9999]

My approach would be:

1. Group by CaseID
2. Create a formula:
[Code {@Assess}]
If {lwcase.status} = "Warrant Issued"
Then 1
Else
If {lwcase.status} = "Cleared by Arrest"
Then 3
Else 2
[/Code]
3. Then for each of the groups, if Maximum({@Assess}, {lwmain.caseid}) = 3 and Minimum({@Assess}, {lwmain.caseid}) = 1, both of your conditions have been met.

Hope this helps.

Cheers
Pete

 

[Kim296]

Thank you Pete,

I was able to do a similar formula to the one you suggested and it does count correct if I put it into the details section and I can have it show the right number in the footer section, but the problem comes in when I try to count the numbers because it trys to count everything in the details section over riding what my correct footer count is. Huh!! I'm stumped. I'm sure I can figure a way around this. I appreciate your suggestion.

Thanks,
Kim

 

[pmax9999]

So why not use my approach and use a running total or variable to do the count?

 

[Kim296]

I did. My formula did the same thing that yours did in the footer. This works perfect for the count on each case number. I may not have explained myself very well. I'm trying to total that "running total" number for each case into one GRAND TOTAL for all case numbers that match the criteria.

I do appreciate your help.