Top N in stacked bar chart

Hi,

I have been trying to display the top N of my report data on a stacked bar chart.
I have a data set that looks like this:

COUNT CODE STACK
10 ABC X
2 ABC Y
11 DEF X
3 ABC Z
5 DEF Y

...

so, total count for ABC = 10+2+3 = 15
DEF = 11+5 = 16

I need to display the first N total, sorted descending by the total count.
I did not find the way to display the top N and sorted with the total count.

For example, with the image that I attached

Find more posts tagged with

Comments

Hans_vd

Hi mas_h 
 
Is your dataset based on a database query? 
You could create a new dataset that does the grouping, the summary and the top n selection like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT * FROM (
SELECT code,
count (*),
row_number () over (ORDER BY count(*) DESC) rn
FROM your_table
GROUP BY code
) WHERE rn < 6
</pre>
 
This query might have some Oracle specific syntax in it, but in most databases you can do something like this I think. 
 
Hope this helps 
Hans

mwilliams

Is there a reason that you're using 2.2.1? Some of the limitations of 2.2.1 make this very difficult as opposed to newer versions. I started working on a solution, but I don't have a deployable version of 2.2.1 to work with and I am given an error that says that persistentGlobalVariables can only be used in the runtime in 2.2.1. What I ended up trying to do was I created a computed column in my dataSet that found the total of each group by ordering my data in my SQL and then using script to keep a running sum for each order. I then was creating an array in my dataSet's beforeOpen script to store the top values into. I then stepped through my dataSet in my onFetch script where I changed the values in the array based on the current value. If it was larger than one in the array, I moved them down in the slots so they were in order. I was then going to store this final array in a PGV and access it in the filter for the chart to limit the chart data to only those values that matched the values in my array. You might give this a try.

This is a very difficult way of doing things for sure, but I was unable to sort tables or filter tables based on an aggregation value, so using a table as a dataSource didn't quite work. I couldn't do the aggregation inside the chart because you couldn't filter by it there either. Also, aggregating over a group isn't available in a chart either. Using the table as a source with the top 5 groups works in later versions I know, for sure. 2.2.1 seems like it could be a difficult task. Maybe I'm missing something easier, I hope.

mas_h

2.2 is because I need to integrate with jdk 1.4. Too bad ..

And 1.4 is because customers do not want the higher version. That makes my life difficult. :huh:
I believe 2.3 needs jdk 1.5, right?
Anyway, I will try your idea and see if it works.

mwilliams

Definitely try doing it in your query if you dataSets are query based!

mas_h

Yes, I have to find the top categories in another query, and then add in where condition in the original query with the categories found. Not an elegant solution, but it works for now.
However, I cannot sort it as the way I wanted (descending by total, but ascending on the label when total are the same for multiple groups). I guess there are the limitation on 2.2.

Hans_vd

Did you go with the row_number solution? 
 
You could add the label in the order by section of the row_number function: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>row_number () over (ORDER BY count(*) DESC, code ASC)</pre>
 
And then order your dataset by the row_number (rn)

mas_h

I'm not able to know the total count until run-time, remember? Because it is a stacked bar graph.

Hans_vd

Hi Mas_h, 
 
I forgot about the extra level "stack". I have a completely new query that should do what you want: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT * FROM (
SELECT tab1.code,
tab1.stack,
count (*),
dense_rank () over (ORDER BY tab2.amount_per_code DESC, tab1.code) rank
FROM your_table tab1,
(SELECT tab2.code
COUNT(*) amount_per_code
FROM your_table tab
GROUP BY tab.code
) tab2
WHERE tab1.code = tab2.code
GROUP BY tab1.code, tab1.stack, tab2.amount_per_code
ORDER BY tab2.amount_per_code
)
WHERE rank <= 5</pre>
 
Let me know if it works 
 
Regards 
Hans