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:
SourceSet | Max MonthDiff | Amount of Max |
---|
A-01 | 12 | 788 |
A-02 | 8 | 456 |
A-04 | 5 | 1080 |
A-05 | 3 | 696 |
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.