Count distinct with max value

Anonymous — Mon, 23 Nov 2015 09:32:10 GMT

Hi fellas,

When I analyze my data with pivot chart, here is my result:

MonthDiff/SourceSet	A-01	A-02	A-04	A-05
-1	2741	1698	1838	291
0	2412			1633
1	3372	1826	2857	1050
2	3777	845	2444	800
3	3117	705	1199	696
4	3006	939	775
5	2375	886	1080
6	2026	303
7	2010	223
8	2192	456
9	1458
10	633
11	909
12	788

With set analysis which result on straight table I want to get is this:

For "Amount of max" column, this formula works fine:

COUNT(DISTINCT IF(AGGR(NODISTINCT MAX(MonthDiff),SourceSet)=MonthDiff,ID))

But I want to do this without AGGR function because when adding a new filter to the table with another field, that doesn't work properly.

When I use this formula, it does work for only first row:

COUNT({$<MonthDiff={'$(=MAX(MonthDiff))'}>} DISTINCT ID)

Any idea?

Thanks a lot in advance.

topic Count distinct with max value in QlikView