Counting specific records than grouping them within a crosstab 1

[AceCool69]

Hi All,

Using CRXI
Connecting to Remedy database

Retrieving details of incidents logged by users over a period of 3 months, unique identifier is the "Case ID". I need to count the number of users that have logged more than 3 incidents, more than 4 incidents and more than 5 incidents. I can use a crosstab to count the number of incidents a user has logged and also get the top 20 users no probs. But canny figure out how to group them as above on to a crosstab:

Text box: "Number of users that have logged more than 3 Incidents"
July Aug Sept Total
More than 3 INCs 143 215 189 547
More than 4 INCs 61 98 82 241
More than 5 INCs 20 26 19 65

I would suppress the column totals as those figures would be meaningless.

I have tried using running totals which count correctly but can't I figure out a way of using them in the crosstab as above. (would then chart of this as well)

Any help much appreciated!

Cheers.

 

[lbass]

You won't be able to use an inserted crosstab. If you don't mind pivoting the crosstab so that the months go down the left side, and the intervals go across the top, then you could insert a group first on date(monthly), and then on user. Then create one running total for each criterion, as in {#>3INCS}, where you select user, distinctcount, evaluate using a formula:

count({table.caseID},{table.user}) > 3

Reset on change of group (date).

Place the running totals in the date group footer, drag the groupname into the group footer, and suppress the details section.

Then insert an advanced layout chart that uses {table.date} as the "on change of" field and the running totals as the summaries.

-LB