1 Reply Latest reply: Dec 27, 2011 3:51 PM by Karl Pover RSS

    Top 25 Values for each Month based on the Expression field in a Bar chart

      Hi all,

      I want to develop a Bar chart which will have 2 stacked bars for each Yearmonth,one stacked bar chart is for field A and one for field B respectively.

      The data that i want in this bar chart is Top 25 Annual deal values for each month and display both the fields in respective stacked bar charts which will be associated with those 25 top annual deal values for that month.(A,B,MONTH,Annual Deal Value,ID are in one Table)

      One solution i think is that if i can script a loop in which in one iteration it will take only one month and that months rows ,sort them desc and then take top 25 for that particular month.In next iteration it will take the next month and so on.

      Can anyone provide any other solution  or provide the solution that i gave in scripting?

        • Top 25 Values for each Month based on the Expression field in a Bar chart
          Karl Pover

          If you want to get rid of the values, the loop sounds alright along with the function First().  To calculate the rank for all the rows, you don't need to use the loop if you order the whole table by Annual Deal Value and MonthYear and then use the inter-row functions like previous or peek to keep a counter that will be your rank and to know when the month changes to start the counter over again.  Also, you can do it in the GUI with the something like the following formula:


          sum( aggr( if( rank( sum(Sales), 4, 1) < 25, sum(Sales), 0),MonthYear)


          The big question I have is how are you going to do a bar chart with 2 stacked bars that depend on different dimensions?  You are going to have to separate it into 2 separate bar charts.