Hi All

I'm trying to create a set of rules, in a system to record time, that highlight when there are data (compliance) issues.

The structure is a

          Service Request (SR),

          with associated Activities (many Activities to one SR) and

          Trackers (many Trackers to one Activity)

          from a Siebel CRM System.

The first Rule is :

A Service Request (SR) with an Open Status must have at least one Activity in an Open Condition.

Why:

Once all physical work activities have been done - then the SR status should be set to Complete.  If more work is required then a new activity associated to the SR should be created.

What I am trying to achieve :

I want to create a check (rule)/Calculated Field in Tableau to highlight/alert which SR Records don't meet the criteria, and to be able to count these against a dimension (i.e. Region - not in sample data for simplicity).

I'm having trouble getting my head around aggregating the data so that it only shows a count for one SR - because the SR Reference is duplicated many times to the number of Activities / Trackers.

I've attached a very simple reconstruction of the data (SR NUM and Activity ID's are autogenerated so please ignore the format) in this example SR3 should be flagged because all the Activities are Closed but the SR is still Open.

Hope this makes sense and any help appreciated.

Best regards

Ian