Top 10 Rank Based on Total of 2 Rows

Trevor Wheadon shared this question 9 months ago
Answered

Hello,

I am creating a chart that shows number of incidents per site (top 10). The chart needs to be a stacked horizontal bar that shows the number of open and number of closed incidents per site.

The Issue I am running into is when I apply a ranking to my report, it ranks the number of closed and number open per site individually, it does not rank based on total incidents for that site (see attached screenshot). This means that my chart mostly only shows the number of closed incidents per site, since those are the higher ranking numbers.

Is there a way for the ranking to look at the total incidents for the site instead of total open or closed?

Comments (4)

photo
1

Hi Trevor,


Thanks for your question!


The way to do this is through an Append Subquery. You will join the queries on the field that you want the rank to be grouped by, in your case 'Site' = 'Site'. Then bring in the 'Number of Incidents' field and use an Advanced Function: Top 10 on this column. You will then be able to move this to the front of the Report and Suppress Duplicates. You could even go further, and add a Subtotal summary on the 'Site' field and aggregate total 'Number of Incidents' as a sum, or bring the 'Number of Incidents' field into the Subquery a second time to give you a clear idea of the total number of incidents. See screenshots below with Ski Teams dataset:


/a1b09c1d491bb6d6be0e598970218f93


/4c588a3a605691441b6299ed8c966e9e


/d40b4f39f9f653843be59057d0dcd3f6


Let me know how you go!


Kind regards,

Simon

photo
1

Hi Simon,

Thank you for the reply!

I added an append sub query onto my report and I can get the total number of incidents now, but it is ignoring the date filter I have on the original query so it gives me a total of all incidents ever created for that site. I can add a date filter onto my sub query, but then the user of the report would have to fill in two filters. I also attempted to link the two date filters, but when I do that my report returns 0 results.

Any thoughts on how I can link these filters?

Thank you,

Trevor

photo
1

Hi Simon,

I was able to link my date filters by using the "Same Operator" option when linking the two filters and my report is now showing correct data!

Thank you very much for the help!

Trevor

photo
1

Hi Trevor,


That's exactly right! You can link filters from the Subquery to the Master query, you might find that you have 2 or 3 filters repeated through the Subquery process, however, this is the intention. Append Subqueries are commonly used for Year to Year comparisons in separate columns, where you can separate data by months for example, and measure a metric over time for use in charts when you desire a separate series for each year.


Let me know if I can go ahead and close this ticket. If you have any further questions relating to this ticket, you can always re-open it at a later date.


Kind regards,

Simon