Top N or Max Count

[detmawin]

Good day,

I have a table with the following fields, monthyear, date, and a field to count on, and dept.  I created a bar chart with a set analysis expression to find the count per dept.  I would like to find the highest count for each month for each dept. 

I used a straight table and sorted on max values, but I am not sure how to get the max count for each month/year in the dimension,and plot on a bar or line chart.

Thank you for your help.

[sunny_talwar]

What is your bar chart's dimension? and you want to see the maximum by month and dept how?

[trdandamudi]

I am assuming your fields are as below:

dimension:

Month

Dept

Expression:

=Max ( aggr ( Count( YourfieldNameToCount ), Month , Dept ) )

Hope this helps, If not please post a small sample.

[detmawin]

Thanks very much for the quick response.  I would like to rank by the highest date in the month per count.  So it is totaling all of the dates as the count.  I would like May 2016, then 5/1,2016, 30(max count), I am getting May 2016, 5/x/2016, count for total month of may instead of just max count for the highest day in the month.

Thanks.

Dimensions:

MonthYear

DATE

DEPT.

I have a set analysis to find the (COUNT({$<Dept = {'Sales'}>} distinct [Shipping_Cost]).  This part works fine.

[sunny_talwar · Accepted solution]

Try this:

Dimensions:

MonthYear

DEPT

Expressions:

1) FirstSortedValue(DATE, -Aggr(Count({$<Dept = {'Sales'}>} distinct [Shipping_Cost]), MonthYear, DEPT))

2) Max(Aggr(Count({$<Dept = {'Sales'}>} distinct [Shipping_Cost]), MonthYear, DEPT))

[detmawin]

Thanks,  When I use rank, it I get a -1 to show that this is the highest rank for the date, but how do I get the actual count to show.

=rank(COUNT({$<Dept= {'Sales'}>} distinct [Shipping_Cost]))<2

I get Date 5/20/2015. Sales -1, instead of the count, but this is the correct date with the highest value.  It just doesn't show the count.

thanks.

[sunny_talwar]

May be this:

If(Rank(Count({$<Dept= {'Sales'}>} distinct [Shipping_Cost]))<2, Count({$<Dept= {'Sales'}>} distinct [Shipping_Cost]))

[detmawin]

Thank you very much @Sunny T