topic Re: Top N or Max Count in QlikView

Top N or Max Count

detmawin — Fri, 10 Jun 2016 17:36:47 GMT

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.

Re: Top N or Max Count

sunny_talwar — Fri, 10 Jun 2016 17:58:58 GMT

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

Re: Top N or Max Count

trdandamudi — Fri, 10 Jun 2016 18:03:42 GMT

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.

Re: Top N or Max Count

detmawin — Fri, 10 Jun 2016 19:02:54 GMT

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.

Re: Top N or Max Count

sunny_talwar — Fri, 10 Jun 2016 19:24:13 GMT

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))

Re: Top N or Max Count

detmawin — Fri, 10 Jun 2016 19:49:13 GMT

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.

Re: Top N or Max Count

sunny_talwar — Fri, 10 Jun 2016 20:05:28 GMT

May be this:

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

Re: Top N or Max Count

detmawin — Tue, 14 Jun 2016 13:39:48 GMT

Thank you very much @Sunny T