Value per Max/Min of Dimension

[SieDLer]

Hi all,

I need some help from the hive! ❤️

I have the following Table:

Customer \ Revenue by year	2018	2019	2020
A	80€	200€	150€
B	-	300€	500€
C	30€	60€	-
D	-	100€	-

I need to get the following results per Table:

Customer \ Revenue	Revenue in earliest year	Revenue in latest year
A	80€	150€
B	300€	500€
C	30€	60€
D	100€	100€

I can correctly detect the earliest and latest year for each customer using min(Year), max(Year) - however I am struggling to get the correct revenue for the latest or earliest year on a customer level.

Example latest year:

Sum(
{<
$Year={$(=MAX(Year))}
>}
Revenue
)

Can anyone help me figure out how to get it right?

Thanks a lot.

[chrismarlow]

Hi,

I don't think you can use Set Analysis like that as the MAX applies across everything & the set is therefore the same for all rows.

You could try something like sum(if(Year=aggr(Min(Year),Customer),Revenue,Null())), so cut down version below;

Cheers,

Chris.