Duplicate Counts in Cross Tab Report

Trevor Wheadon shared this question ago

Answered

Hello,

I am trying to create a report that shows the total number of incidents, along with total number that have met a resolution/response service level agreement, broken out by incident priority.

To achieve this, I have a total number of incident column, as well as 2 calculated fields to calculate the number of incidents that met their resolution/response targets. The calculated fields look as follows:

CASE WHEN Goal Category = 'Incident Response Time' AND Measurement Status = 'Met' THEN COUNTDISTINCT ( Application Instance ID ) END

CASE WHEN Goal Category = 'Incident Resolution Time' AND Measurement Status = 'Met' THEN COUNTDISTINCT ( Application Instance ID ) END

The issue I am facing is when one of these fields is included in the report, my total number of incidents double and I'm not sure why. Does anyone have any idea as to why this would happen? If it helps, most of my fields are from one table, while the resolution/response fields listed in the above calculations are from a different table. I have also attached a screenshot of my report with the doubled total number of incidents.

Thank you,

Trevor

Files:

The same question

Replies (2)

Eric Hagfors ●

Hi Trevor,

Thanks for reaching out to support with your question. I will check to see if I can get a content creation specialist on the line ot see if they know a good potential way to resolve this issue.

Thanks,

Eric

Reply URL

Trevor Wheadon ●

Hello,

This can be marked as resolved.

I was able to eliminate the duplicate counts by creating 2 append subqueries, 1 for response met and one for resolution met.

Thank you,

Trevor

Reply URL