Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I started with a table like this.
Dim 1 | Dim 2 | Dim 5 | Dim 6 | Expression |
A | C | 2015-01-12 | S | SUM(something) |
A | C | 2015-02-13 | T | .. |
A | D | 2015-04-19 | U | |
B | E | 2015-01-03 | V | |
B | E | 2015-02-12 | X |
The result I wanted was to get the highest date (Dim 5) over Dim 1 and 2.
With an aggregation like this:
=Date(Aggr(DISTINCT max(Dim 5),Dim 1, Dim 2),'YYYY-MM-DD')
..I got something good to start with:
Dim 1 | Dim 2 | Dim 5 | Dim 6 | Expression |
A | C | 2015-02-13 | S | SUM(something) |
A | C | 2015-02-13 | T | .. |
A | D | 2015-04-19 | U | |
B | E | 2015-02-12 | V | |
B | E | 2015-02-12 | X |
Now to my question - how do I filter Dim 6 so I only get the one that 'connects' to the highest date?
As a result as below?
Dim 1 | Dim 2 | Dim 5 | Dim 6 | Expression |
A | C | 2015-02-13 | T | SUM(something) |
A | D | 2015-04-19 | U | .. |
B | E | 2015-02-12 | X |
May be replace Dim6 with this:
=Aggr(FirstSortedValue(Dim6, -Dim5), Dim1, Dim2, Dim5)
=Aggr(FirstSortedValue([Dim 6], -[Dim 5]), [Dim 1], [Dim 2])
Or just use this as your expression and leave Dim 5 and Dim 6 as is:
=Aggr(FirstSortedValue(Aggr(Sum(something), [Dim 1], [Dim 2], [Dim 5], [Dim 6]), -[Dim 5]), [Dim 1], [Dim 2])
Thank you, that did the trick.
Thank you, but in your example you get the lowest date.
Awesome