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.
