I have a text column with this value:
2018 - 2021
I would like to find an expression that can calculate the years between this range.
The output should be an EnumList:
2018, 2019, 2020, 2021
Set up a Table that is just a list of years.
Then pull a matching List from that:
FILTER(
year ,
AND(
[year] <= NUMBER( INDEX( SPLIT( [text] , " - " ) , 2 )
[year] >= NUMBER( INDEX( SPLIT( [text] , " - " ) , 1 )
)
)
Thank you very much @Marc_Dillon. Instead of using an extra table, could I also hardcode the years list in the expression?
I canโt think of any way. Because you need to filter an existing list of dates, by comparing against your start and end. There is no way to do something like this:
FILTER(
LIST(x,y,z)
...
)
Like this?
TOP(
LIST(
(YEAR([Date]) - 0),
(YEAR([Date]) - 1),
(YEAR([Date]) - 2),
...,
(YEAR([Date]) - N),
),
M
)
Where N is the most number of years youโll allow, M is the number you need now, and M <= N.
Thank you @Marc_Dillon and @Steve these both very good solutions.
User | Count |
---|---|
33 | |
30 | |
30 | |
19 | |
17 |