Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.